#DIV/0

[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

 

[Aladin Akyurek]

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

 

[Peo Sjoblom]

One way

=AVERAGE(IF(ISNUMBER(climate1),climate1))

entered with ctrl + shift & enter

 

[JE McGimpsey]

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.