Average & #DIV/0

  • Thread starter Thread starter RayD
  • Start date Start date
R

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
 
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)
 
Try something like

=IF(ISERROR(AVERAGE(A1:A10)),"",AVERAGE(A1:A10))
Click to expand...

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.
 
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.
 
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.
 
J.E. McGimpsey said:
Unfortunately, COUNT() ignores them as well..., so

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

returns no more useful information than
Click to expand...
....

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.
 
Back
Top