Calculate standard deviation only on values between x and y

Question

i have a range of data in column a called mycol. Some values are clearly errors (ie, less than 0 or over 100000. If I don't omit those values the stdev is highly skewed. Therefore I want to calculate the stdev only on the values >0 and < 100000. Can you please explain the best way to do this?

I thought about creating a named range consisting of only those within the limits, but I'm not sure if named ranges are dynamic in that way.

Answer 1

Order the column in excel by ascending or descending. Then find all values before 0 and delete. Next find all values above say 100 000? delete them. Just a simple way to get rid of the values. CTRL + F to find 0 in excel once you have filtered by desc or ascending.

Answer 2

With array formulas this is pretty straight forward.

Note Array Formulas are entered with: Ctrl + Shift + Enter

Use an IF statement with the references to factor out the values you don't want to include i.e. >0 and < 100000 and then just surround it with the proper STDEV formula. Here is an example with STDEV.S and using the range A1:A5

=STDEV.S(IF(((A1:A5>0)*(A1:A5<100000)=1),A1:A5))

If you have mycol as a named range:

=STDEV.S(IF(((mycol>0)*(mycol<100000)=1),mycol))