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:
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
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
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?
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
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
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
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
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)))}
Ross,
Do you mind if I write a topic on my own blog about this…..?
Jelle-Jeroen
Ross,
I toke the liberty to post it, If you’ve got any problems with that I will remove it.
Jelle-Jeroen
Hi Jelle-Jeroen,
Thanks, thats fine no worries,
Cheers
Ross