Average & #DIV/0

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
 
H

Harlan Grove

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

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

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

Harlan Grove

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