macros

R

rjc_29

Can anyone advise me how i would write a macro to show the followin
colourised in a spreadsheet
“Cold” Crime is more than 2 SDs below the mean
“Cool” Crime is between 1 and 2 SDs below the mean
“Normal” Crime is between 1 SD below the mean and 1 SD above the mean
“Warm” Crime is between 1 and 2 SDs above the mean
“Hot” Crime is more than 2 SDs above the mean

SD = standard deviation
 
J

Jerry W. Lewis

Details are lacking. Have the mean and SD been calculated already?
What colors are you wanting to use (or is it sufficient to return the
specified text? Where are you wanting this action to take place? Why a
macro if text-only is OK?

For text only, you can use nested IFs (there is a limit of 7 nested
functions, but this only requires five)

=IF((value-mean)/sd<-2,"Cold",IF((value-mean)/sd<-1,"Cool",IF((value-mean)/sd<0,"Normal",IF((value-mean)/sd<1,"Warm",IF((value-mean)/sd<2,"Hot")))))

It would be faster if mean and sd were calculated elswhere, but
recalculating them five times each within the formula (using AVERAGE and
STDEV worksheet functions) would only increase the nesting levels to
six, and thus would still work.

Changing colors would require VBA, since conditional formatting only
supports three separate conditions.

In VBA, you would use a Select Case statement

Select Case (value-mean)/sd
Case Is <-2
' cold
Case Is <-1
' cool
....
Case Else
' hot
End Select

You can call worksheet functions from VBA as in
Application.Average(data_range)

Jerry
 
R

rjc_29

Sorry didn't explain it very well. I have already calculated th
standard deviations. What i hope to do is set up a macro that wil
look for values eg. 1 below standard deviation and assign it a colou
such as blue, 1 above orange 2 or more above red etc
 
Top