-1

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Jeff Johnson
  • 53
  • 1
  • 4
  • I'm voting to close this question as off-topic because it's a math question and off-topic for Stack Overflow. – nalply Jun 03 '15 at 10:13

2 Answers2

1

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))
chancea
  • 5,858
  • 3
  • 29
  • 39
  • 1
    Thanks so much @chancea. That did the trick. What is the "*" and the "=1" doing? Is that because true evaluates to 1 for both of them, so 1*1 = 1? – Jeff Johnson Jun 02 '15 at 20:49
  • @JeffJohnson Yeah and actually you can remove the `=1` because what you said is completely accurate `1=true` so therefore `IF(1,...)` will work just fine. I just left it in there for readability. – chancea Jun 02 '15 at 21:13
0

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.

Jason Lesser
  • 59
  • 4