Just came across this. It’s Apples GUI guide lines, some good stuff in there for sure.
May 2008
The Evil Midnight Bomber What Bombs at Midnight
Recently I was in a business meeting, the chap from the “other side” suggested that i should stop making posts in the wee small hours! Since I started my new job time has been a bit harder to come by than it was before so I end up thinking about Excel later in the night!!!! Here’s my question to other bloggers; how and when do you write your posts, whats your motivation and and how do you make the time?
And for no reason other than I thought of it, he’s a link to the main man of midnight stuff, from one of my fave cartoons ever! Even his grammar is right! (for me anyway!!!)
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:
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
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