Options for DIV#/0

[jday]

I have a formula that is VERY long and contains three nested components that
require a 'division' function. Any of these three 'divisors' could equate to
zero, which of course will cause a DIV/#0 error. I cannot have this, because
the result of this cell needs to be used in other formulas, which would just
permeate the issue. However, I do not want to nest 3 additional
"IF(ISERROR...." statements within an already unwieldy formula to cover the 3
division components. Is there any sort of function that can be incorporated
at the start of my formula that basically says "If the final result of this
formula is an error, then return a zero, otherwise return the calculated
result"?

 

[Luke M]

You have right idea, but instead of doing 3 seperate checks, just do one.
Since you didn't give example of your formula...

=IF(ISERROR(YourFormula),0,YourFormula)

 

[Elkar]

If you're using Excel 2007, then you can use the IFERROR function:

=IFERROR(your_formua,"")

For older versions of Excel, you'll need to use the "unwieldly" approach.
But you don't have to repeat it 3 times, just once.

=IF(ISERROR(your_formula),"",your_formula)

HTH
Elkar

 

[Paul C]

I have used a shorter version like this

if (A1*A2*A3<>0,"YOUR FORMULA",0)

Where A1,A2,A3 would be replaced by your three divisor.

This would leave any other errors "intact" and only isolate the DIV#/0. I
have fould this helpful since sometimes other errors crop up that you may
wnat to see.

 

[jday]

I was not familiar with this new "IFERROR" function in Excel 2007. This is
exactly what I needed! The ISERROR version would work too, but the formula
is 3 wrapped lines long, so to reference that formula twice within the cell
would have been TOO MUCH!! Thanks for your help.

 

[jday]

That is a great point too. My issue is that each of the 'divisors' is
actually a lengthy SUMIF statement -- not a cell reference or value. I will
definitely keep this in mind for future use though---thanks!

 

[Rick Rothstein]

I was not familiar with this new "IFERROR" function in Excel 2007.

In the future, mention that you are using Excel 2007 when you ask your
questions... that way, you can get answers that are targeted to your setup.