handling #Div/0! errors

[rockhammer]

Is there a smarter way to do this?

In order to avoid the unsightly #Div/0! appearing in printouts, where I do a
divide, I almost always do this:

=if(isnumber(A1/B1),A1/B1,"--")

Is there a way to tell excel to always show a specific value (in my case
"--") whenever a #Div/0! shows up, so that I don't have to handle the
exceptions in the cell formulae myself?

The example above is really too simple. Often the division are built from
much more complicated calculations which can make the whole if statement a

200 letters long.

Thanks.

 

[Rick Rothstein \(MVP - VB\)]

Under normal circumstances, the only time you should be getting a #DIV/0!
error is if the denominator is equal to zero, so that should be a sufficient
test...

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

For your more complicated formula, just isolate and test all the
denominators for all of the divisions and test them for 0. For example,

=IF(OR(B1=0,C1=0),"--",A1/B1+A1/C1)

Rick

 

[rockhammer]

Thank you, Rick & Dana.

It's good to hear excel 2007 has =iferror(). I'm still using 2003 and really
hesitant to switch since I'll face major backward compatibility issues w/ my
employer's & friends' systems. Thanks.

 

[LarryP]

I just checked to confirm my recollection: Excel 2003 has ISERR, ISERROR,
and ERROR,TYPE. One or the other (or a combination) should do what you need.