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

[JEM]

Currently:

=AVERAGE(AV15:AV429)

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

Thanks.

 

[Frank Kabel]

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

 

[JEM]

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.)

 

[Frank Kabel]

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

 

[Alan]

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,

 

[JEM]

Thank AGAIN guys!

JEM

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,

 

[Harlan Grove]

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)

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.