Standard Deviation Function

nj1994

New Member
Joined
Oct 4, 2019
Messages
3
Hello,
I am wondering if there is a way to do a standard deviation ifs function. I am trying to take the standard deviation from a specific criteria within a spread sheet so for example: one of the criteria I need to fulfill is <=8.5 & >=2.9 & >=18 <=26. So I will need 4 criteria that will need to be met before the standard deviation of the specific column is found. I hope this makes sense, thanks in advance for the help.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Try something like this array formula.
Formula must be entered with CTRL-SHIFT-ENTER.
Excel Workbook
ABCDEFGHI
1Data
26St. Dev.6
31010.18332
412
53
Sheet
 
Upvote 0
Caveat: AhoyNC used STDEV.S (aka STDEV). That is an approximation of the standard deviation of a larger population (set of numbers), based on the assumption that the selected numbers are a "representative" (statistical) sampling of the population.

Use STDEV.P (aka STDEVP) to calculate the exact standard deviation of the selected numbers.
 
Upvote 0
Try something like this array formula.
Formula must be entered with CTRL-SHIFT-ENTER.

ABCDEFGHI
1 Data
2 6 St. Dev. 6
3 10 10.18332
4 12
5 3

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:38px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
D3{=STDEV.S(IF(((B2:B9>=2.9)*(B2:B9<=8.5))+(($B$2:$B$9>=18)*(B2:B9<=26)),B2:B9))}

<tbody>
</tbody>
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


It worked, thank you very much. I minimized the search field because if I used the full range it returned a Div/0 error. Is there a workaround to get past this? replace 0's with a certain number so that I can use the full field?
 
Upvote 0
I may not understand your question, but in the example below I use a range from B2:B9 in cell D4 and then in cell D5 I used the whole column B:B without an error.
Make sure you enter the formula with CTRL-SHIFT-ENTER. If you don't you will get the Div/0 error.

If you didn't use CTRL-SHIFT-ENTER.
-Put the cursor in the cell with the formula.
-Press F2 key for edit
-Then press CRTL-SHIFT-ENTER.

Joeu2004 is right I did use the standard dev. for a sample, not knowing if your data was the entire population or a sample.
Excel Workbook
ABCD
1Data
26
310St. Dev.
41.732051
531.732051
60
76
830
97
10
Sheet
 
Upvote 0
if I used the full range it returned a Div/0 error. Is there a workaround to get past this? replace 0's with a certain number so that I can use the full field?

It is difficult to say what the problem is unless you post your formula. Ideally, upload an example Excel file to a file-sharing website and post the download URL here, so we can see the data that causes the #DIV/0 error.

No, you do not want to "replace 0's with a certain number". That would change the std dev.

AFAIK, the only way that a properly array-entered STDEV or STDEVP expression would return a #DIV/0 error is if less than 2 values for STDEV or no values for STDEVP are selected by the conditional expression.

First, do you want to avoid all zeros in the std dev calculation? Or do you just want to avoid empty cells, which might be interpreted as zero, depending on the formula that you use?

In order to avoid all zeros, array-enter (press ctrl+shift+Enter instead of just Enter) a formula of the form:

=STDEVP(IF(A1:A1000<>0, A1:A1000))

Caveat: Note that I do not put a comma after the second A1:A1000. If you put a comma, Excel will include zeros in the std dev calculations.

Better:

=IFERROR(STDEVP(IF(A1:A1000<>0, A1:A1000)), "")

To include actual zeros in the data in the std dev calculation, array-enter (press ctrl+shift+Enter instead of just Enter) a formula of the form:

=STDEVP(IF(ISNUMBER(A1:A1000), A1:A1000))

Note: It is not a good idea to use A:A. That might cause Excel to create and process temporary arrays of 1+ million elements.
 
Upvote 0
=STDEVP(IF(A1:A1000<>0, A1:A1000))

Caveat: Note that I do not put a comma after the second A1:A1000. If you put a comma, Excel will include zeros in the std dev calculations.

Well, that is correct. But I meant to put the caveat after the ISNUMBER expression.

The point is: if we put a comma after the value-if-true part, the missing value-if-false will be interpreted as zero, not FALSE. That should not cause a #DIV/0. But it would miscalculate the std dev.
 
Upvote 0
Hi, so I am a bit confused and looking for clarification on whether or not I can do this and how. I have a column in excel with 1000+ data points, some of which are empty with a "-" in it. I want to take the standard deviation of this entire column only if it meets certain criteria in other columns. For example one of the sets of criteria I will need is mentioned above I want the standard deviation of column A 1:1000 if column B 's corresponding row meets the criteria of <=8.5 or >=2.8 AND if column C's criteria is met which is >=18 or <=26. I hope this makes a little more sense, please let me know and thanks for the help.
 
Upvote 0
Caveat: AhoyNC used STDEV.S (aka STDEV). That is an approximation of the standard deviation of a larger population (set of numbers), based on the assumption that the selected numbers are a "representative" (statistical) sampling of the population.

Use STDEV.P (aka STDEVP) to calculate the exact standard deviation of the selected numbers.

Not exactly.

STDEV ad STDEV.S are the unbiased estimator of standard deviation, assuming not every member of a population is measured, but that you are sampling from a subset of the entire the population, which is almost always the case. Dot S for Sample.

STDEV.P is the computed standard deviation, assuming that you are measuring every member of the population. Dot P for Population. STDEV.P underestimates the standard deviation if the entire population is not included.

The difference is whether you divide by N (population) or by N-1 (sample) under the radical.
 
Last edited:
Upvote 0
Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Excel Workbook
ABCDE
1Data
268.419
3108.124Std. Dev
48.2251.892969
531.512
60322
76420
83011.616
977.319
Sheet
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top