Ignore Zeros with STDEVP

[tester100099]

I'm trying to create formula that will calculate the population standard deviation that ignores all zeros. For example, I want to get a result of 5.969309262 instead of 9.393982839
when I calculate the population standard deviation of the following list:

17
19
12
0
25
25
9
24
0

How do I need to adjust the STDEVP formula to get this to work?

Thanks!

 

[jeffreybrown]

Hi & Welcome to the MrExcel Forum,

Try =STDEV(IF($B2:$C2<>0,$B2:$C2,""))) (Adjust the ranges to suit your needs)

Enter with Ctrl +Shift + Enter -- not just enter and not manually.

If succesful you will find {} around the formula.

 

[tester100099]

jrb,

Thanks for the help! I adjusted the formula since I am using a population, and now it works exactly as I anticipated.

 

[tester100099]

jrb,

I tried to apply this to the AVERAGE formula but I wasn't able to get it to work. What do I need to use to get AVERAGE to ignore zeros as well?

Thanks again!

 

[jeffreybrown]

Just change Stdev to Average and then reapply the Ctrl + Shift + Enter

AVERAGE(IF($B2:$C2<>0,$B2:$C2))

 

[tester100099]

Turns out I simply failed to used ctrl shift enter.

When I searched around the forums, I noticed that this is often necessary. Why is that?

 

[jeffreybrown]

This can explain it far better than I ever could...

http://www.cpearson.com/excel/ArrayFormulas.aspx