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?
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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
3,025
Office Version
  1. 2010
Platform
  1. 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,130,013
Messages
5,639,553
Members
417,097
Latest member
miguel_z

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
Top