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:
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
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
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
Cheers
Hi Jelle-Jeroen,
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