Replace #DIV/0!

  • Thread starter Thread starter Alan
  • Start date Start date
A

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
 
Thank you Frank! And i wont post my messages to multiple groups [thats
cheating!]
I appreciate your help.
Alan

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

--
Regards
Frank Kabel
Frankfurt, Germany

Alan said:
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
Click to expand...
Click to expand...
 
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
 
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.
 
Back
Top