#DIV/0


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
 
Ad

Advertisements

A

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

Peo Sjoblom

One way

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

entered with ctrl + shift & enter
 
Ad

Advertisements

J

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

#DIV/0! 2
#DIV/0! 3
#DIV/0! 1
IF with #DIV/0! 1
DIV/0! 2
IF with #DIV/0! 2
#DIV/0 2
#div/0 1

Top