Options for DIV#/0
 

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"?

Options for DIV#/0
 

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)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"jday" wrote:

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"?

Options for DIV#/0
 

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


"jday" wrote:

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"?

Options for DIV#/0
 

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.
--
If this helps, please remember to click yes.


"Elkar" wrote:

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


"jday" wrote:

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"?

Options for DIV#/0
 

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.

"Elkar" wrote:

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


"jday" wrote:

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"?

Options for DIV#/0
 

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!


"Paul C" wrote:

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.
--
If this helps, please remember to click yes.


"Elkar" wrote:

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


"jday" wrote:

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"?

Options for DIV#/0
 

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.

--
Rick (MVP - Excel)