May 2008

Apple UI

Just came across this. It’s Apples GUI guide lines, some good stuff in there for sure.

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:

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:
  1. Function SDFromCount(Counts As Range, Values As Range)
  2. Dim Scores() As Double
  3.  
  4. Dim i As Integer
  5. Dim j As Integer
  6. Dim k As Integer
  7. k = 1
  8.  
  9.  
  10. For i = 1 To Values.Cells.Count
  11. j = j + Counts.Cells(1, i).Value
  12. Next i
  13. ReDim Scores(1 To j)
  14.  
  15.  
  16. For i = 1 To Values.Cells.Count
  17. For j = 1 To Counts.Cells(1, i).Value
  18. Scores(k) = Values.Cells(1, i).Value
  19. k = k + 1
  20. Next j
  21. Next i
  22.  
  23.  
  24. SDFromCount = WorksheetFunction.StDev(Scores)
  25.  
  26. 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