CODEWITHSUNDEEP
excelvbastandard-deviation
Tim
Tim

Reputation: 3

Weighted Standard Deviation in VBA

Im trying to write a weighted Standard Deviation function in VBA. 2 ranged inputs are supposed to output a value. However, it returns a #value error.

Edit: added worksheet.function

Public Function StDevWeighted(data As Range, weight As Range) As Double

    'https://stats.stackexchange.com/questions/6534/how-do-i-calculate-a-weighted-standard-deviation-in-excel
    
    Dim mean, top, bottom
    
    mean = WorksheetFunction.SumProduct(data, weight) / WorksheetFunction.Length(data)
    
    top = WorksheetFunction.SumProduct(weight, (data - mean) ^ 2)
    bottom = ((WorksheetFunction.Length(data) - 1) / WorksheetFunction.Length(data)) * WorksheetFunction.Sum(weight)
    
    StDevWeighted = WorksheetFunction.SQRT(top / bottom)
End Function

Upvotes: 0

Views: 222

Answers (1)

DS_London
DS_London

Reputation: 4261

So, unfortunately you have to do the work to calculate the square differences by yourself, item-by-item, and store the answers in a temporary array (see comments for why). I haven't checked the maths ... above my pay-grade!

Public Function StDevWeighted(rngData As Range, rngWeight As Range) As Double
    Dim dMean As Double
    Dim dTop As Double
    Dim dBottom As Double

    Dim vData As Variant
    vData = rngData
   
    dMean = WorksheetFunction.SumProduct(rngData, rngWeight) / rngData.Count

    Dim vSqDiff() As Variant
    ReDim vSqDiff(1 To UBound(vData, 1), 1 To UBound(vData, 2))

    For r = 1 To UBound(vData, 1)
        For c = 1 To UBound(vData, 2)
            vSqDiff(r, c) = (vData(r, c) - dMean) ^ 2
        Next c
    Next r

    dTop = WorksheetFunction.SumProduct(rngWeight, vSqDiff)
    dBottom = ((rngData.Count - 1) / rngData.Count) * WorksheetFunction.Sum(rngWeight)

    StDevWeighted = Sqr(dTop / dBottom)
End Function

PS. I tested this from my spreadsheet directly using the Debugger.

Upvotes: 1

Related Questions