## [Solved] Average a column but ignore cells with zero

### [Solved] Average a column but ignore cells with zero

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?
Last edited by Hagar Delest on Tue Mar 26, 2019 8:54 am, edited 1 time in total.
Reason: tagged solved
openoffice 4.1.1 on Windows 10
manntis

Posts: 11
Joined: Sat May 28, 2016 4:49 am

### Re: Average a column but ignore cells with zero

Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine

robleyd
Moderator

Posts: 2983
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

### Re: Average a column but ignore cells with zero

That worked a treat

Solution:

=AVERAGEIF(AB4:AB29;">0")
openoffice 4.1.1 on Windows 10
manntis

Posts: 11
Joined: Sat May 28, 2016 4:49 am

### Re: Average a column but ignore cells with zero

If you actually want non-zero values including negatives to be calculated, use "<>0".
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine

robleyd
Moderator

Posts: 2983
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia