Replace #DIV/0!

[Alan]

Hi -

I am using Excel 2002 and have a bunch of formulas that result in #DIV/0!.
I know why this happens and theris NOT a problem with the formula, it is
simply based on the fact that we are dividing by 0.
The error is quite irritating [and confusing to my bosses that dont
understand]. Is there a way to simply have the error default to 0 when the
#DIV/0! ?

Thank you !

Alan

 

[Frank Kabel]

Hi
change your formula to something like
=IF(N(A1)=0,0,B1/A1)

 

[Alan]

Thank you Frank! And i wont post my messages to multiple groups [thats
cheating!]
I appreciate your help.
Alan

Hi
change your formula to something like
=IF(N(A1)=0,0,B1/A1)

--
Regards
Frank Kabel
Frankfurt, Germany

Hi -

I am using Excel 2002 and have a bunch of formulas that result in #DIV/0!.
I know why this happens and theris NOT a problem with the formula, it is
simply based on the fact that we are dividing by 0.
The error is quite irritating [and confusing to my bosses that dont
understand]. Is there a way to simply have the error default to 0 when the
#DIV/0! ?

Thank you !

Alan

 

[Bob Umlas]

You can do it without changing formulas ==>:
Select ALL the cells, use Format/Conditional Formatting, change "Cell Value
is" to "Formula is", enter =ISERROR(A1), then click the Format button, the
Font tab, choose white.
the "A1" in "ISERROR(A1) is if the active cell is A1. If the active cell is
F3, use F3.

Bob Umlas
Excel MVP

 

[Guest]

Embed an IF formula

IE IF("Current Formula"=0,0,"Current Formula)

If your current formula produces a result of zero, then, 0
will be returned instead of DIV\0.