Standard Deviation Function

[nj1994]

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.

 

[AhoyNC]

Try something like this array formula.
Formula must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1		Data
2		6		St. Dev.		6
3		10		10.18332
4		12
5		3
Sheet

 

[joeu2004]

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.

 

[nj1994]

Try something like this array formula.
Formula must be entered with CTRL-SHIFT-ENTER.

	A	B	C	D	E	F	G	H	I

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: center"]Data[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ccccff]#ccccff[/URL] , align: right"]6[/TD]

[TD="align: center"]St. Dev.[/TD]

[TD="align: right"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]10.18332[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]12[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ccccff]#ccccff[/URL] , align: right"]3[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula 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?

 

[AhoyNC]

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
	A	B	C	D
1		Data
2		6
3		10		St. Dev.
4				1.732051
5		3		1.732051
6		0
7		6
8		30
9		7
10
Sheet

 

[joeu2004]

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.

 

[joeu2004]

=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.

 

[nj1994]

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.

 

[Jon Peltier]

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.

 

[AhoyNC]

Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E
1	Data
2	6	8.4	19
3	10	8.1	24		Std. Dev
4		8.2	25		1.892969
5	3	1.5	12
6	0	3	22
7	6	4	20
8	30	11.6	16
9	7	7.3	19
Sheet