May 20th, 2008

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