Calculating stdev.s excluding a certain value

[droot]

Hi,

I'm trying to calculate the stdev.s of a dataset that isn't 26.

This is the formula I'm using - {=STDEV.S(IF(Sheet1!V:V<>"26",Sheet1!V:V))} and it gives me 8.3
If i calculate the same data set but remove all the 26's using find and replace, i get 18.9

What am I doing wrong?

 

[Legacy 436357]

Maybe:

{=STDEVA(IF(V2:V100<>26,V2:V100))}

 

[droot]

That gives the same result as the first (8.3)

 

[droot]

Sorry, just seen the edit. I think it worked!! Testing on other datasets to makes sure

 

[joeu2004]

An important factor that might be overlooked: certainly the range V:V, and perhaps even the range V2:V100, will include unintended zero values in the calculation. This is because when the "value if true" range is evaluated, any empty cells are treated as zero, unlike a straight-forward STDEV(V2:V100) expression.

To demonstrate, enter 1, 2, 3 and 4 into alternating cells A1, A3, A5 and A7. Note the difference between =STDEV(A1:A7) and array-entered { =STDEV(IF(A1:A7<>26,A1:A7)) }. Now, enter zero into A2, A4 and A6. Note that STDEV(A1:A7) now returns the same result as the array-entered formula.

If you want to ignore empty cells as well as cells that equal 26, array-enter the following:

{ =IF(STDEV(IF(Sheet1!V2:V100<>"", IF(Sheet1!V2:V100<>26, Sheet1!V2:V100))) }

I cannot see any reason to use STDEVA instead of STDEV. On the contrary, STDEVA might give an unintended result in the cases that make the two functions different.

The important take-aways are: use limited ranges (V2:V100) instead of whole-column ranges (V:V); and do not put quotes around numbers ("26") that are intended to be treated as such.

 

[droot]

Thank you both for your help.
@joeu2004 - thanks for the explanation, very helpful.