AVG not including '0' or value or blank cells.

  • Thread starter Thread starter JEM
  • Start date Start date
J

JEM

Currently:

=AVERAGE(AV15:AV429)

How can I change this formula to ignore '0' or blank cells when returning
the average?

Thanks.
 
Hi
blank cells are automatically excluded. To also exclude
zeros use the following array formula (entered with
CTRL+SHIFT+ENTER):
 
Thanks Frank,

It works, but I have one question. Here's the story: the column I using this
for averages three other columns first. (Let's say it's the forth of four
columns.)

When I use the simple =AVERAGE(AV15:AV429), it returns the average including
zero values, which doesn't always give me accurate information for averages.

When I apply the new formula, =AVERAGE(IF(AV15:AV429<>0,AV15:AV429)) (as an
array)
it excludes zero values, which is exactly what I need. Currently, the sheet
is blank and the aveage of the three columns reported in the fourth is zero,
so the cell shows a #DIV/0 error. Is this normal?

(Even if I click on 'Ignore Error' the warning mark remains.)
 
Hi
the error is normal as you have zero entries and so the
sum (which is zero) is divided by zero (the number of
entries).
Some workarounds
1. Use a formula like
=IFISERROR(AVERAGE(...)),"",AVERAGE(...))
2. You may use conditional format to hide the errors:
- select the column (lets say AV1:AV200)
- goto 'format - Conditional format'
- enter the formula
=ISERROR(AV1)
- choose a white font color
 
Yes it is, here is a non-array formula which will return zero if all values
are zero or all the cells are empty,

=IF(SUM(AV15:AV429)<>0,SUM(AV15:A429)/COUNTIF(AV15:AV429,">0"),0)

Regards,
 
Thank AGAIN guys!

JEM

Alan said:
Yes it is, here is a non-array formula which will return zero if all values
are zero or all the cells are empty,

=IF(SUM(AV15:AV429)<>0,SUM(AV15:A429)/COUNTIF(AV15:AV429,">0"),0)

Regards,
Click to expand...
 
Yes it is, here is a non-array formula which will return zero if all values
are zero or all the cells are empty,

=IF(SUM(AV15:AV429)<>0,SUM(AV15:A429)/COUNTIF(AV15:AV429,">0"),0)
Click to expand...

Just to note that this assumes OP only has positive numbers to sum. Safer and
more explicit to use

=IF(COUNTIF(AV15:AV429,">0"),SUMIF(AV15:A429,">0")/COUNTIF(AV15:AV429,">0"),0)

If you're certain you have no negative numbers, you could just use

=SUM(AV15:AV429)/(COUNTIF(AV15:AV429,">0")+(SUM(AV15:AV429)=0))

which saves a function call.
 
Back
Top