[Solved] Average a column but ignore cells with zero

[manntis]

I have a column AB which calculates percentages. I'm using the following formula in it:

=IF(AA4>0;(AA4/K4))

This is to avoid a divided by zero error if the value of either the entry in column AA or column K is zero. Instead of the DIV error it returns 0.00%

I have this formula duplicated all down column AB for 25 entries. At the bottom I'm taking the average of all the percents using:

=AVERAGE(AB4:AB29)

The problem, dear readers, is that it counts the zeroes and includes that in the average, bringing the average down. I want the average of just the percents that are greater than zero (or including less than zero as well, if that's possible) and ignoring any zero values.

Thoughts?

[robleyd]

AVERAGEIF() - see the Help - F1 for more information.

[manntis]

That worked a treat

Solution:

=AVERAGEIF(AB4:AB29;">0")

[robleyd]

If you actually want non-zero values including negatives to be calculated, use "<>0".