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?
[Solved] Average a column but ignore cells with zero
[Solved] Average a column but ignore cells with zero
Last edited by Hagar Delest on Tue Mar 26, 2019 8:54 am, edited 1 time in total.
Reason: tagged solved
Reason: tagged solved
openoffice 4.1.1 on Windows 10
Re: Average a column but ignore cells with zero
AVERAGEIF() - see the Help - F1 for more information.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Average a column but ignore cells with zero
That worked a treat
Solution:
=AVERAGEIF(AB4:AB29;">0")
Solution:
=AVERAGEIF(AB4:AB29;">0")
openoffice 4.1.1 on Windows 10
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
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers