Average & #DIV/0

[RayD]

Hello everybody

When I try to apply the following formula I get the dreaded #DIV/0

=IF(average(E8:E372)=0,"",average(E8:E372))


Anyone care to take a shot at this?

Many thanks

 

[Chip Pearson]

Ray,

Try something like

=IF(ISERROR(AVERAGE(A1:A10)),"",AVERAGE(A1:A10))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

 

[RayD]

Worked perfectly Chip..... Thanks!

 

[Harlan Grove]

Try something like

=IF(ISERROR(AVERAGE(A1:A10)),"",AVERAGE(A1:A10))

ISERROR can trap useful errors (#REF!, #NAME?) which indicate things may be
going seriously wrong. In this case,

=IF(COUNT(A1:A10),AVERAGE(A1:A10),"")

would be a viable alternative.

 

[J.E. McGimpsey]

ISERROR can trap useful errors

Unfortunately, COUNT() ignores them as well..., so

=IF(COUNT(A1:A10),AVERAGE(A1:A10),"")

returns no more useful information than

=IF(ISERROR(AVERAGE(A1:A10)),"",AVERAGE(A1:A10))

though it's undoubtedly a bit more efficient.

 

[J.E. McGimpsey]

Should have added...


for the case where there's no entries in A1:A10. It obviously
returns the error when a value is in a1:a10.

 

[Harlan Grove]

Unfortunately, COUNT() ignores them as well..., so

=IF(COUNT(A1:A10),AVERAGE(A1:A10),"")

returns no more useful information than

....

when there are no numbers in the common argument list. Agreed. However, if
there's at least one number in the common argument list, it works.

So, how to fix this? Best I can come up with is

=IF(COUNT(A1:A10)+ISERROR(SUM(A1:A10)),AVERAGE(A1:A10),"")

which only traps no numbers in A1:A10.