After reading and commenting on the Standard Deviation of Counts at Methods in Excel I decided to award the topic a post on my own blog ( with the blessing of Ross )
The problem is to get the Standard Deviation for a number of counts of scores. You can achieve this by coding an UDF or by a combination of worksheet functions. Each with the obvious drawbacks.
The test example consists out of the following data:
The formula for the Standard Deviation I used was:
I named the two main ranges “values” and “scores”, defined:
N as SUM(counts),
the first summation as SUM(MMULT(values^2,TRANSPOSE(counts))),
and the second summation as SUM(MMULT(values,TRANSPOSE(counts))))^2
The combination of these parts resulted in:
{=SQRT((SUM(counts)*SUM(MMULT(values^2,TRANSPOSE(counts)))-((SUM(MMULT(values,TRANSPOSE(counts))))^2))/(SUM(counts)*(SUM(counts)-1)))}
If the scores represent the entire population instead of a sample you should change it to
{=SQRT((SUM(counts)*SUM(MMULT(values^2,TRANSPOSE(counts)))-((SUM(MMULT(values,TRANSPOSE(counts))))^2)))/SUM(counts)}
My choice for coding a UDF would be:
Function STDEVCOUNTS(Counts As Range, Values As Range, bPartial As Boolean)
Dim N As Long: Dim i As Long
Dim dSum As Double: Dim SValues() As Double
ReDim SValues(1 To Values.Cells.Count)
For i = 1 To Values.Cells.Count
SValues(i) = Values(1, i) * Values(1, i)
Next i
With WorksheetFunction
N = .Sum(Counts)
dSum = N * .Sum(.MMult(SValues, .Transpose(Counts)))
dSum = dSum - .Sum(.MMult(Values, .Transpose(Counts))) ^ 2
dSum = dSum / (N * (N + bPartial))
End With
STDEVCOUNTS = dSum ^ 0.5
End Function
Slightly slower ( about 5% ) but less code is my second attempt with a little help from Bob Phillips from the Excel User Group.
Function STDEVCOUNTS(Counts As Range, Values As Range, bPartial As Boolean)
Dim N As Long: Dim i As Long: Dim dSum As Double
With WorksheetFunction
N = .Sum(Counts)
dSum = N * Evaluate(”SUM(MMULT(” & Values.Address(, , , True) & _
“^2,TRANSPOSE(” & Counts.Address(, , , True) & “)))”)
dSum = dSum - .Sum(.MMult(Values, .Transpose(Counts))) ^ 2
dSum = dSum / (N * (N + bPartial))
End With
STDEVCOUNTS = dSum ^ 0.5
End Function
Thoughts on this code or on the functions….?
Cheers
Jelle-Jeroen