## Excel Standard Deviation based on criteria

### Excel Standard Deviation based on criteria

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

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

Is there a way in Excel chart to automatically calculate standard deviation
and create upper and lower control limit horizontal lines?  I can get
vertical data point standard deviation lines utilizing error bars but not
one horizontal line.    Thanks.

8. Florida