Excel Standard Deviation based on criteria

Excel Standard Deviation based on criteria

Post by Geoff Lambe » Sat, 05 Jul 2003 11:40:54



In Excel 200, I have 2 columns of numbers.

I want to calculate the mean and standard deviation of all of those
numbers in column 2 for which the corresponding numbers in column 1
match a certain criterion, e.g.

Mean and SD of all in column L where where Fn=10

For the mean, I can do it thus

=SUMIF($F$15:$F$3000,"="&P6,$L$15:$L$3000)/COUNTIF($F$15:$F$3000,"="&P6)

[here my criterion is in cell P6 rather than being specified as a
number]

or thus:

={SUM(($F16:$F3000=P6)*$L16:$L3000)/COUNTIF($F$15:$F$3000,"="&P6)}

here an array formula is being used to calculate the numerator.

But if I try to calculate the standard deviation with an equivalent
formula

={STDEVP(($F16:$F3000=P6)*$L16:$L3000)}

I get an answer 10 times too small.

What syntax should I be using, or do I have to do it an entirely
different way (e.g. with sums of sqares and squares of sums, as per
the base formula for SD?

Geoff Lambert

 
 
 

Excel Standard Deviation based on criteria

Post by Geoff Lambe » Sat, 05 Jul 2003 12:40:37


Solved

Well you do it this way, don't you?...

=STDEVP(IF(F15:F3000=P6,L15:L3000))

When all else fails, read the instructions.... In this case a
contribution by Nick Manton 5 years ago, based on Chip Pearson's
website

Geoff Lambert