Archive for May, 2008

Standard Deviation of Counts

Tuesday, 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:

Sample Data

The formula for the Standard Deviation I used was:

Standard Deviation Formula

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

Posted in UDF, Worksheet Functions | 2 Comments »

How Excel solves all my problems

Thursday, May 8th, 2008

Last week I was on holliday and we had to divide 3 bottles of wine among ten friends.
We could have written our names on little papers and draw 3 names from the lot,
but why do something like that when you have a computer with excel.
So I wrote a array function that returns a permutation from a set of data.

I put the 10 names in A1:A10 and then selected C1:C3 and inserted =PERMUTATION(A1:A10)
finally I pressed shift ctrl enter

and voila ( sorry for the french ) a random permutation of 3 names…….

the source for this small miracle is below

Function PERMUTATION(ByRef rSource As Range) As Variant

Dim PermCol As Collection: Dim Cell As Range: Dim Result() As Variant
Dim iIndex As Long: Dim i As Long: Dim j As Long

ReDim Result(1 To Application.Caller.Rows.Count, 1 To Application.Caller.Columns.Count)

Set PermCol = New Collection

i = 1

For Each Cell In rSource

PermCol.Add CStr(Trim(Cell.Value)), CStr(i): i = i + 1

Next Cell

For i = 1 To Application.Caller.Rows.Count

For j = 1 To Application.Caller.Columns.Count

iIndex = WorksheetFunction.RandBetween(1, PermCol.Count)
Result(i, j) = PermCol(iIndex)
PermCol.Remove (iIndex)

Next j

Next i

PERMUTATION = Result

End Function

I don’t know any real life use for this function besides the dividing of winebotles I told you about.
But if somebody has any use for this beautiful function please let me know…

cheers

Jelle-Jeroen

link to this post on the excel user group with comments and other solution

Posted in UDF | 2 Comments »