# Standard deviation on multiple criteria not working

#### gwfami

##### New Member
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

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Works as expected for me.

CDEFGH
LMNOP
2A16/1/20040.1183783.2
A6/1/200430.026457513TRUE
3A26/1/20040.1183793.21
A6/21/200434.949676757TRUE
4A36/1/20040.1183833.25
A7/21/200430.400790885TRUE
5A16/21/20040.14152210.87
A8/17/20040#DIV/0!
6A26/21/20040.14286320.45
A9/21/20040#DIV/0!
7A36/21/20040.14249517.82
A10/20/20040#DIV/0!
8A17/21/20040.1454212.9
A11/16/20040#DIV/0!
9A27/21/20040.1453122.15
A12/15/20040#DIV/0!
10A37/21/20040.1453312.28
A5/4/20050#DIV/0!

<tbody>
</tbody>
Rich (BB code):
``````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.

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.

Last edited:
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.

CDEFGH
LMNOP
2A16/1/20040.1183783.2
A6/1/200430.026457513TRUE
3A26/1/20040.1183793.21
A6/21/200434.949676757TRUE
4A36/1/20040.1183833.25
A7/21/200430.400790885TRUE
5A16/21/20040.14152210.87
A8/17/20040#DIV/0!
6A26/21/20040.14286320.45
A9/21/20040#DIV/0!
7A36/21/20040.14249517.82
A10/20/20040#DIV/0!
8A17/21/20040.1454212.9
A11/16/20040#DIV/0!
9A27/21/20040.1453122.15
A12/15/20040#DIV/0!
10A37/21/20040.1453312.28
A5/4/20050#DIV/0!

<tbody>
</tbody>
Rich (BB code):
``````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.

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.

Replies
5
Views
304
Replies
0
Views
290
Replies
2
Views
6K
Replies
3
Views
1K
Replies
4
Views
8K

1,196,308
Messages
6,014,583
Members
441,828
Latest member
cofracr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back