CODEWITHSUNDEEP
excelvba
Sarah
Sarah

Reputation: 71

calculating weighted standard deviation error

I want to calculate the weighted mean and weighted standard deviation. I was able to calculate the mean but not the standard deviation. How should I go about it?

     With Application.WorksheetFunction


        Range("AH" & 2) = .SumProduct(Columns(7), Columns(8)) / .Max(Columns(8))  'This code works very well. It calculates the mean


        Dim Nprime As Double

        Nprime = .CountIf(Range("H2:H" & lengthRows), "<>" & 0)  'This code works well


        Range("AM" & 2) = 2 * .SQRT(.SumProduct((Columns(7) - Columns(34)) ^ 2, Columns(8)) / ((Nprime - 1) * .Sum(Columns(8))) / Nprime) 'This line does not work. It should calculate the weighted standard deviation.

Range("AM" & 2) = Evaluate("2*SQRT(SumProduct((Columns(7) - weightedMean)^2), Columns(8)) / ((Nprime - 1) * .Sum(Columns(8))) / Nprime)") 'This line code evaluates with an answer of #VALUE! probably due to the titles in Row1, how do I fix the code to only evaluate the numerical values within the columns?

        End With

Upvotes: 0

Views: 243

Answers (1)

CMArg
CMArg

Reputation: 1567

Not shure about the "weighted" AV or SD, but maybe you can adapt the following. Code loads data in an array, and then gets AV and SD.

Private Sub CommandButton1_Click()

    Dim MyArray() As Variant
    Dim lAV As Double
    Dim lSD As Double
    Dim i As Integer

    lLastRow = Worksheets("MyData").UsedRange.Rows.Count
    ReDim MyArray(1 To 1) As Variant

    For i = 1 To lLastRow
        MyArray(UBound(MyArray)) = Worksheets("MyData").Cells(i, 7).Value * Worksheets("MyData").Cells(i, 8).Value
        ReDim Preserve MyArray(1 To UBound(MyArray) + 1) As Variant 'now array is 1 element longer
    Next

    'Now MyArray contains desired data.
    'Get average and SD

    lAV = Application.WorksheetFunction.Average(MyArray)
    lSD = Application.WorksheetFunction.StDev(MyArray)

    'write results (be shure not to overwrite your data)
    Worksheets("MyData").Cells(1, 10) = "Average: "
    Worksheets("MyData").Cells(1, 11) = lAV
    Worksheets("MyData").Cells(2, 10) = "SD: "
    Worksheets("MyData").Cells(2, 11) = lSD
End Sub

Upvotes: 1

Related Questions