Applying excel SD function to a range.

[Rich B.]

Hi All:
I have a macro that calculates various turnaround times for laboratory work. I would like to add the SD function to different parameters. The mean is calculated from a range (=SUMIF L2:L20000,"STAT",J2:J20000)/P11) I am not sure how to apply the excel function STDEV to a cell in order to show the standard deviation of the appropriate range. Hope someone can help.

Thanks: Rich

 

[Damon Ostrander]

Hi Rich,

You can do this using an array formula. I believe that what you want is:

{=STDEV(((L2:L20000="STAT"))*J2:J20000)}

You must enter this as an array formula using Ctrl-Shift-Enter without the curly braces (the curly braces will be added by Excel to identify that it is an array formula). This will give the standard deviation of only those elements in column J that have a corresponding "STAT" in column L.

Damon

 

[Rich B.]

Perfect !! Thanks Damon that is exactly what I wanted it to do. I can apply it to the other priorities on the turnaround time report.

Rich