Getting the average of a column, eliminating zero values.

[shmizzy]

I want the average of column B. But there are many 0 values. I want just the average of the non zero vals. Sorting is not an option. Any ideas?

 

[Peter_SSs]

shmizzy

Welcome to the Mr Excel board!

Try something like this:
=AVERAGE(IF(B1:B100<>0,B1:B100))
which must be confirmed with Ctrl+Shift+Enter not just Enter.

 

[Aladin Akyurek]

=AVERAGE(IF($B$2:$B$400 > 0, $B$2:$B$400))

which needs to be confirmed with control+shift+enter, not just with enter.

This type of formula do not allow whole column references.

Otherwise:

=SUMIF(B:B,">0")/COUNTIF(B:B,">0")

 

[Barry Katcher]

This should also work:

=SUM(A1:A100)/COUNT(A1:A100)

 

[shmizzy]

cool. thanks.