Standard Deviation of Counts

NB: See comments for errors and corrections to this code!

Today I needed to get the Standard Deviation for a number of counts of scores. Like this:

countscores1.PNG

I dont know of a way to do this in the worksheet, and a quick google didnt turn anyhting up eirther, so it was off to VBA, this is what I came up with:

[VBA]
Function SDFromCount(Counts As Range, Values As Range)
Dim Scores() As Double

Dim i As Integer
Dim j As Integer
Dim k As Integer
k = 1

For i = 1 To Values.Cells.Count
j = j + Counts.Cells(1, i).Value
Next i
ReDim Scores(1 To j)

For i = 1 To Values.Cells.Count
For j = 1 To Counts.Cells(1, i).Value
Scores(k) = Values.Cells(1, i).Value
k = k + 1
Next j
Next i

SDFromCount = WorksheetFunction.StDev(Scores)

End Function
[/VBA]

This works with data in rows (one row, many columns), you would have to swap the counter i to the row index to get it to work with a column of data.

Any better ways? Thoughts on this code?

Cheers

Comments

  1. Jelle-Jeroen says:

    Using your function I get a Standard Deviation equal to 1.4142135623731

    But if you manually calculate the Standard Deviation of the 167 scores the answer is 1.01215468673352 ( asuming this is a partial sample of the whole population)

    I ajusted your code

    [VBA]
    Function SDCOUNTS(Counts As Range, Values As Range)

    Dim Scores() As Double

    Dim i As Integer
    Dim j As Integer
    Dim k As Integer

    k = 1

    ReDim Scores(1 To WorksheetFunction.Sum(Counts))

    For i = 1 To Values.Cells.Count
    For j = 1 To WorksheetFunction.Sum(Counts.Columns(i))
    Scores(k) = Values.Cells(1, i).Value
    k = k + 1
    Next j
    Next i

    SDCOUNTS = WorksheetFunction.StDevP(Scores)

    End Function
    [/VBA]

    Cheers

  2. ross says:

    Hi Jelle-Jeroen,

    [vba]
    ReDim Scores(1 To WorksheetFunction.Sum(Counts))
    [/vba]
    Doh!!!!!

    Mine semed to work for my data – i did check the results!!! maybe we have it set up diffrently?

  3. Jelle-Jeroen says:

    Ross,

    What was your Standard deviation…?
    Did you have 167 scores….?

    I’m dutch and didn’t understand your remark: Doh!!!!
    Do you appreciate my remark or didn’t you

    Jelle-Jeroen

  4. ross says:

    Hi Jelle-Jeroen,
    I recheck the function, you are right, it works correctly for some sets, but is incorrect for others! I’m not sure why, will have to look into it a bit more,

    re Doh:
    its what Homer says when re realises hes made a mistake – like i did by looping, rather than just add up the totals in the range, – i did appreciate your remarks, thanks.
    http://www.arar93.dsl.pipex.com/mds975/Images/homer_simpson_doh_02.gif

  5. Rob says:

    a sumproduct and a bit of maths should do the trick (if i understand you correctly).

    for the top line of results,:
    =SQRT(SUMPRODUCT((values_range-mean)^2,count_range))/(n-1)) and entered as an array formula (ctrl-enter).

    it wouldn’t extend simply to a multirow version but you could just have an extra column of ‘total deviations’ and then add em up, divide by n-1 and sqrt.

    Rob

  6. ross says:

    Hi Rob,
    Thanks I’ll take a look at that, it looks like it makes sense. I did try to use a SP, but i got a bit stuck with zeros!
    Cheers
    Ross

  7. Jelle-Jeroen says:

    Ross,

    After reading Rob’s post I toke his way of thinking and wrote an array function for all the rows.
    Its a little bit long but checked out correctly….. ( I think )

    {=SQRT((SUM(counts)*SUM(MMULT(values^2,TRANSPOSE(counts)))-((SUM(MMULT(values,TRANSPOSE(counts))))^2))/(SUM(counts)*(SUM(counts)-1)))}

  8. Jelle-Jeroen says:

    Ross,

    Do you mind if I write a topic on my own blog about this…..?

    Jelle-Jeroen

  9. Jelle-Jeroen says:

    Ross,

    I toke the liberty to post it, If you’ve got any problems with that I will remove it.

    Jelle-Jeroen

  10. Ross says:

    Hi Jelle-Jeroen,
    Thanks, thats fine no worries,
    Cheers
    Ross

Submit a Comment