Using ISERROR to Solve #DIV/0 in a formula

[Leigh Douglass]

Hi

Apologies if i am repeating someone elses question, but can someone help me
enter the ISERROR function into this formula to get rid of the #DIV/0 message?

=IF(G43+C43=0,0,(G43/C43))

Basically cell C43 is a zero, so when the formula calculates the last
section I am getting the #DIV/0 error. Can I add the ISERROR function to
display a '0' instead or anything else for that matter?

Thanks in advance for your help.

 

[Duke Carey]

You have a few alternatives:

=IF(G43+C43=0,0,if(iserror(G43/C43),"some message",G43/c43)

=IF(G43+C43=0,0,if(C43=0,"some message",G43/c43)

=IF(OR(G43+C43=0,C43=0),0,G43/C43)

 

[RagDyeR]

Try this:

=IF(OR(G43+C43=0,C43=0),0,(G43/C43))
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Hi

Apologies if i am repeating someone elses question, but can someone help me
enter the ISERROR function into this formula to get rid of the #DIV/0
message?

=IF(G43+C43=0,0,(G43/C43))

Basically cell C43 is a zero, so when the formula calculates the last
section I am getting the #DIV/0 error. Can I add the ISERROR function to
display a '0' instead or anything else for that matter?

Thanks in advance for your help.

 

[David Biddulph]

If you want to use ISERROR you can use
=IF(G43+C43=0,0,IF(ISERROR(G43/C43),"error",G43/C43))
but that may get confused with other sorts of error.
If you are merely trying to cope with the divide by zero, then perhaps
better to use
=IF(G43+C43=0,0,IF(C43=0,"divide error",G43/C43))

 

[Toppers]

=IF(C43=0,0,G43/C43)

 

[Tevuna]

Excel 2003: =IF(G43+C43=0,0,IF(C43=0,0,G43/C43))
Excel 2007: =IFERROR(IF(G43+C43=0,0,(G43/C43)),0)

 

[Tevuna]

Toppers, you don't provide for C34= 1 and G34= -1
1 + (-1) =0
1 / (-1) = (-1)

 

[Toppers]

The "G43+C43" test is totally redundant. If C43 is zero, then G43 must be
zero and vice versa.

 

[Leigh Douglass]

Duke

You are a life saver. Thanks very much.

You have a few alternatives:

=IF(G43+C43=0,0,if(iserror(G43/C43),"some message",G43/c43)

=IF(G43+C43=0,0,if(C43=0,"some message",G43/c43)

=IF(OR(G43+C43=0,C43=0),0,G43/C43)

 

[Toppers]

Got me!!! OK!

Toppers, you don't provide for C34= 1 and G34= -1
1 + (-1) =0
1 / (-1) = (-1)

 

[Tevuna]

Yeah, but the fellow wants a zero even when C43 is not zero; in cased where
C43 and G43 cancel each other by opposite signs.

 

[Harlan Grove]

=IF(C43=0,0,G43/C43)

....

Or avoid all function calls.

=(C43<>0)*G42/(C43+(C43=0))

Not recommended, just FTHOI.

 

[bj]

maybe
=if(or(c43=0,C43+G43=0),0,g4/c43)

 

[Harlan Grove]

maybe
=if(or(c43=0,C43+G43=0),0,g4/c43)

....

The OP was confused when giving C43+G43=0 in the origin example formula.
That formula didn't come close to matching the prose specs. There's no need
to check C43+G43=0 unless the OP doesn't want instances of C43 <> 0 and C43
= -G43 to result in 0 rather than -1. Off the top of my head, I can't think
of any reason why the OP would want 0 as result instead of -1 but would want
other negative ratios when C43 is nonzero and has the opposite sign of G43.