Conditional calculation of the standard deviation.

[TPFKAS]

Hi there,

I have a table with two columns (a1:b200).
I need to calculate the standard deviation of the values in column B, but only for those values where a condition in column A is being met.
For example: column A contains only the numbers 0 and 1 and I want only to include all numbers in column B in the calculation of the standard deviation where the value in column A is 1.

Can this easily be done with a single formula?

Thanks in Advance!

 

[Eric W]

Sure, for example:

	A	B	C	D	E
1	Col A	Col B
2	0	748		307.8	342.1146
3	1	328
4	0	833
5	1	876
6	0	820
7	0	511
8	0	441
9	0	943
10	1	824
11	0	575
12	0	974
13	0	811
14	1	99
15	0	494
16	1	174
17	1	870
18	1	890
19	1	182
20	0	97
21	0	493
22	1	390
23	0	44
24	0	567
25	1	165

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula D2 =STDEV.S(B2:B25) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula E2 {=STDEV.S(IF($A$2:$A$25=1,$B$2:$B$25))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

If your standard deviation formula is D2, then the E2 array formula only includes the values where column A = 1. Make sure to enter it with Control+Shift+Enter.

 

[TPFKAS]

Thanks for your prompt reply.
Works like a charm. I was just reading some stuff about array operations. Never knew about it before. Another extra piece of wisdom. ?

 

[Eric W]

I don't think anyone knows all the tricks of Excel! Arrays are particularly obscure, and particularly useful! Glad this one works for you.