Calculating stdev.s excluding a certain value

droot

New Member
Joined
Oct 18, 2018
Messages
27
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?
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
L

Legacy 436357

Guest
Maybe:

{=STDEVA(IF(V2:V100<>26,V2:V100))}
 
Last edited by a moderator:

droot

New Member
Joined
Oct 18, 2018
Messages
27
Sorry, just seen the edit. I think it worked!! Testing on other datasets to makes sure
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,737
Office Version
2010
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,472
Messages
5,468,823
Members
406,612
Latest member
pedad

This Week's Hot Topics

Top