Conditional Standard Deviation

[bp32]

Hey Everyone,

I would like to calculate standard deviations for data in column B, but only if the value in column A is equal to a particular value. For sake of argument, let's say I want the STDV calculated for all values in column B where column A = "ABC" (column A here being "general" or "text").

Any ideas how to structure the formula?

Many thanks.

 

[barry houdini]

You can use an array formula like this

=STDEV(IF(A2:A100="ABC",B2:B100))

confirmed with CTRL+SHIFT+ENTER

 

[bp32]

Thanks, Barry.

I tried it a few times and keep getting an error.

Here's what I used: =STDEV(IF(E2:E1227="NYY",P2:P1227)). I also am hitting CTRL+SHFT+ENTER.

Could it be that I need to change the format of the text in column E?

 

[barry houdini]

That formula looks fine. Are you using CSE correctly?

If you have the formula in a specific cell then select that cell and then press F2 to select the formula. Now hold down CTRL and SHIFT keys and press ENTER - if done correctly then you'll get curly braces like { and } around the formula in the formula bar.

If that doesn't work....what error are you getting?

 

[bp32]

It's telling me it is a divide by zero error.

I altered the formula, since there were 11 rows where the value was 0. Here's the new formula:

{=STDEV(IF(E12:E1227="NYY",P12:P1227))}

I think the problem is with the IF function. I tried using a cell as reference and I get the same error. I tried converting the names to numbers, and that seems to do the trick.

Thanks!