Standard deviation on multiple criteria not working

[gwfami]

I'm trying to calculate standard deviation based on multiple criteria. I've used suggestions show on this site and on Google, but it keeps returning the SD of the entire list, not just those that match.

Here's the formula I'm using:

=STDEV.S(IF($C$2:$C$10=$L2,IF($E$2:$E$10=$M2,$H$2:$H$10))) followed by Shift-CTRL-Enter.

SD of first three rows of data should be ~ 0.0224<style>table { }tr { }col { }br { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style>, Formula is showing ~7.223, and strangely enough, anything below that row is showing #DIV/0! (which I haven't been able to figure it out) even though the formula is appropriate.

<colgroup><col style="width:65pt" width="87"> </colgroup><tbody>
</tbody>

Here's the data in CSV format (Sorry, couldn't figure out how to attach the document itself).

Location,Sample,Date,Volume cubic meter,Nbr,Nbr/L,StDev,,,Location,Date,# Samples,StDev
A,1,6/1/04,0.118,378,3.20,0.022421621,,,A,6/1/04,3,7.222321331
A,2,6/1/04,0.118,379,3.21,,,,A,6/21/04,3,#DIV/0!
A,3,6/1/04,0.118,383,3.25,,,,A,7/21/04,3,#DIV/0!
A,1,6/21/04,0.14,1522,10.87,,,,A,8/17/04,0,#DIV/0!
A,2,6/21/04,0.14,2863,20.45,,,,A,9/21/04,0,#DIV/0!
A,3,6/21/04,0.14,2495,17.82,,,,A,10/20/04,0,#DIV/0!
A,1,7/21/04,0.145,421,2.90,,,,A,11/16/04,0,#DIV/0!
A,2,7/21/04,0.145,312,2.15,,,,A,12/15/04,0,#DIV/0!
A,3,7/21/04,0.145,331,2.28,,,,A,5/4/05,0,#DIV/0!



Thanks,
GWFAMI

 

[joeu2004]

Works as expected for me.

	C	D	E	F	G	H		L	M	N	O	P
2	A	1	6/1/2004	0.118	378	3.2		A	6/1/2004	3	0.026457513	TRUE
3	A	2	6/1/2004	0.118	379	3.21		A	6/21/2004	3	4.949676757	TRUE
4	A	3	6/1/2004	0.118	383	3.25		A	7/21/2004	3	0.400790885	TRUE
5	A	1	6/21/2004	0.14	1522	10.87		A	8/17/2004	0	#DIV/0!
6	A	2	6/21/2004	0.14	2863	20.45		A	9/21/2004	0	#DIV/0!
7	A	3	6/21/2004	0.14	2495	17.82		A	10/20/2004	0	#DIV/0!
8	A	1	7/21/2004	0.145	421	2.9		A	11/16/2004	0	#DIV/0!
9	A	2	7/21/2004	0.145	312	2.15		A	12/15/2004	0	#DIV/0!
10	A	3	7/21/2004	0.145	331	2.28		A	5/4/2005	0	#DIV/0!

<tbody>
</tbody>

Formulas:
O2: { =STDEV.S(IF($C$2:$C$10=$L2,IF($E$2:$E$10=$M2,$H$2:$H$10))) }
P2: =O2=STDEV(H2:H4)
P3: =O3=STDEV(H5:H7)
P4: =O4=STDEV(H8:H10)
Copy O2 into O3:O10

Formulas displayed with curly brackets {...} are array-entered. Type the formulas without the curly brackets, then press ctrl+shift+Enter instead of just Enter. Excel displays the curly brackets in the Formula Bar to indicate that the formula is array-entered.

I presume that the different st dev values in column O are because you did not display 15 significant digits in column H; so my values might not have the same precision that your values do. When we save as CSV, values are written as they appear in the cell, which might not their full precision.

In my case, the #DIV/0 errors are to be expected since there is no data in column H for those dates in column M.

I suspect you unconsciously corrected your mistake in your posting.

In particular, you say that you array-entered by pressing ctrl+shift+Enter instead of just Enter.

But I duplicate the misbehavior of the formula in O2 when I press just Enter, not ctrl+shift+Enter. And I duplicate the #DIV/0 errors in O3 and O4 when I copy the normally-entered formula down the column. Ergo, I think that in fact, you did not press ctrl+shift+Enter, after all.

 

[gwfami]

Thanks,
The problem was with the shift-ctrl-enter. I'm using a mac and it requires you to use the right side "shift-ctrl" keys not the left side.

Thanks.

GWFAMI

Works as expected for me.

	C	D	E	F	G	H		L	M	N	O	P
2	A	1	6/1/2004	0.118	378	3.2		A	6/1/2004	3	0.026457513	TRUE
3	A	2	6/1/2004	0.118	379	3.21		A	6/21/2004	3	4.949676757	TRUE
4	A	3	6/1/2004	0.118	383	3.25		A	7/21/2004	3	0.400790885	TRUE
5	A	1	6/21/2004	0.14	1522	10.87		A	8/17/2004	0	#DIV/0!
6	A	2	6/21/2004	0.14	2863	20.45		A	9/21/2004	0	#DIV/0!
7	A	3	6/21/2004	0.14	2495	17.82		A	10/20/2004	0	#DIV/0!
8	A	1	7/21/2004	0.145	421	2.9		A	11/16/2004	0	#DIV/0!
9	A	2	7/21/2004	0.145	312	2.15		A	12/15/2004	0	#DIV/0!
10	A	3	7/21/2004	0.145	331	2.28		A	5/4/2005	0	#DIV/0!

<tbody>
</tbody>

Formulas:
O2: { =STDEV.S(IF($C$2:$C$10=$L2,IF($E$2:$E$10=$M2,$H$2:$H$10))) }
P2: =O2=STDEV(H2:H4)
P3: =O3=STDEV(H5:H7)
P4: =O4=STDEV(H8:H10)
Copy O2 into O3:O10

Formulas displayed with curly brackets {...} are array-entered. Type the formulas without the curly brackets, then press ctrl+shift+Enter instead of just Enter. Excel displays the curly brackets in the Formula Bar to indicate that the formula is array-entered.

I presume that the different st dev values in column O are because you did not display 15 significant digits in column H; so my values might not have the same precision that your values do. When we save as CSV, values are written as they appear in the cell, which might not their full precision.

In my case, the #DIV/0 errors are to be expected since there is no data in column H for those dates in column M.

I suspect you unconsciously corrected your mistake in your posting.

In particular, you say that you array-entered by pressing ctrl+shift+Enter instead of just Enter.

But I duplicate the misbehavior of the formula in O2 when I press just Enter, not ctrl+shift+Enter. And I duplicate the #DIV/0 errors in O3 and O4 when I copy the normally-entered formula down the column. Ergo, I think that in fact, you did not press ctrl+shift+Enter, after all.