#DIV/0

  • Thread starter Thread starter Tom G
  • Start date Start date
T

Tom G

Hi All,
I am using =AVERAGE(climate1), where climate1 contains numerous cells with
#DIV/0 which cause the formula not to work.
I want excel to ignore all cases where cells in the range climate1 include
#DIV/0

thanks
 
=AVERAGE(IF(ISNUMBER(climat1),climat1))

which must be confirmed with control+shift+enter instead of just with enter.

Or:

=SUMIF(climat1,"<>#DIV/0!")/MAX(1,COUNT(climat1))
 
You've gotten a couple of responses that work, but why would you want to
allow #DIV/0 errors in the first place?

If it's a matter of data not being filled in yet, it seems better to me
to use something like

=IF(A1="","",B1/A1)

Since AVERAGE() ignores text, you then don't need to use the
convolutions of an array-entered formula.
 
Back
Top