[Solved] Average a column but ignore cells with zero

Discuss the spreadsheet application

[Solved] Average a column but ignore cells with zero

Postby manntis » Tue Mar 26, 2019 1:14 am

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

Postby robleyd » Tue Mar 26, 2019 1:24 am

AVERAGEIF() - see the Help - F1 for more information.
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
User avatar
robleyd
Moderator
 
Posts: 2983
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Average a column but ignore cells with zero

Postby manntis » Tue Mar 26, 2019 3:12 am

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

Postby robleyd » Tue Mar 26, 2019 3:33 am

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
User avatar
robleyd
Moderator
 
Posts: 2983
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia


Return to Calc

Who is online

Users browsing this forum: Google [Bot], Majestic-12 [Bot] and 16 guests