5

I'm trying to calculate standart deviation, but I need a way to ignore the cells with value 0.

For example, in the following image, standart deviation of J4:M4 should be 3.766 (ignoring M4). Since the location of the zeros are not fixed (not in the same column) I can't just use J4:L4. For the 6th row, I need to ignore K6.

I need a flexible solution, like =AVERAGEIF(J4:M4,">0"). How should I proceed?

enter image description here

  • Are columns J:M user entered values or are they formulas? – Steven May 8 '17 at 20:48
  • @Steven They are also formulas. – akinuri May 8 '17 at 20:49
6

You will need to use this array formula:

=STDEV.P(IF(J4:M4>0,J4:M4))

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then excel will put {} around the formula.

enter image description here

5

Wrap the formulas in the input cells as such:

=if( OriginalFormula <= 0, "", OriginalFormula )

The STDEV (and several other)functions ignore any non-numeric value.

  • 1
    This is a much better solution if the formulas can be changed. – Scott Craner May 8 '17 at 21:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.