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:
Function SDFromCount(Counts As Range, Values As Range)
Dim Scores() As Double
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