• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate standard deviation if not a certain value

Excel_beginner

New Member
Imagine I have 10 columns and I wish to calculate the standard deviation. However, I want to exclude a certain value from the calculation due to error in data.

79704Here is the formula and I want to exclude if the value is=100.
How do I do that.
=STDEV.S(A2:C2)
 

Excel_beginner

New Member
Code:
=STDEV.S(IF(A2:C2<>100,A2:C2))
Thanks, seems to work fine. How would this work if I had multiple values that I want to exclude. For example value 98, -1 and 100.
Of course I can write
STDEV.S(IF(A2:C2<=100,A2:C2))
But I want to learn with multiple IF.
 

p45cal

Well-Known Member
Something along the lines of:
Code:
=STDEV.S(FILTER(A2:D2,(A2:D2<>100)*(A2:D2<>-1)))
or
Code:
=STDEV.S(FILTER(A2:D2,(A2:D2<>100)*(A2:D2<>-1)*(A2:D2<>98)))
 
Top