Excluding Cells from Standard Deviation Formula

**Raistlin** · 12-17-2012, 04:09 PM

Hey guys,

I have a question regarding the standard deviation formula in excel. I have a column for which I want to find the standard deviation. Every value for this column should be between 0 and 1000 (the column contains lag days). However, when this data is imported into excel, a very limited amount of the information is imported as either a negative number or a very high number, such as 45000.

So, my question is, is there a way to write a standard deviation formula for one column that will exclude numbers less than 0 and greater than 1000 from the calculation?

Thanks,
Marty

**Ace_XL** · 12-17-2012, 04:35 PM

Try this array formula

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter and not just Enter

**Raistlin** · 12-17-2012, 05:44 PM

I can get an answer using this formula, but it obviously is not the correct answer. I tried two variations:

=STDEV(IF((B:B<1000)*(B:B>=0),B:B))
and
=STDEV(IF((B:B<1000)*(B:B>0),B:B)) (no equal sign)

Using the first option is giving me values that are much too small to be correct. The data set is not that tight, but I am getting sub .1 deviations. Using the second formula, without the equal sign, is giving me deviations that seem much too large. For instance, I am getting a deviation of 9.15 for a dataset with an average of 3.24. I do not have many numbers above, say, 20 so I know by looking that stdev number can't be right.

I will have to calculate the stdev manually for one of my columns to be sure of the correct answer when I find the right formula, but I am 99.9% sure the results I got with these formulas did not work. This is, however, the first formula I have found that hasn't given me an error message when attempting to enter the formula.

Thanks,
Marty

**daddylonglegs** · 12-17-2012, 05:48 PM

If you refer to the whole column then blank cells will be treated as zeroes in that first formula, hence why the values are too low. Either only use the populated range in the formula or specifically exclude blanks like this

=STDEV(IF((B:B<1000)*(B:B>=0)*(B:B<>""),B:B))

Did you confirm with CTRL+SHIFT+ENTER - if done correctly you get curly braces like { and } around the formula

**Ace_XL** · 12-17-2012, 05:51 PM

Referencing the entire column would mean that blank cells would be treated as 'zero', hence your first formula with the ">=0" will yield wrong results. having said that, the second formula with the "<0" should give correct results.

Make sure you confirm with Ctrl+Shift+Enter, else upload a sample worksheet if you still run into issues