Home > Excel, UDF > Standard Deviation of Counts

Standard Deviation of Counts

May 17th, 2011 admin

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

Categories: Excel, UDF Tags: ,
Comments are closed.