Excel 2003 - Replace error message with a zero

[BlueHorizon]

Hi smart people!!

I have a client who uses a formula that returns the #DIV0 error message. They expect this because some cells are dividing by a zero.

However, they asked if there is a way to replace the #DIV0 error message with a zero value. I tried an IF statement using as the logical argument: [cell reference]=#DIV0. But that returns its own error message.

Hope I made this clear enough for a response. Thanks!!

Best,
Blue Horizon

http://www.bluehorizontraining.com

 

[xlhelp]

=if(iserror("your formula"),0,"your formula")

Me transmitte sursum, Caledoni!

 

[KenWright]

Downside with that approach is that it will also suppress any other errors, and that may well result in bad data being suppressed.

Personally I would simply have an IF formula that looked at the denominator and then if that was 0 return 0, else do the division:-

=IF(A2=0,0,A1/A2)

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[BlueHorizon]

Thank you both!! These are exactly the solutions I was hoping I'd get!

Many thanks!!
Kathy

Best,
Blue Horizon

http://www.bluehorizontraining.com

 

[Rofeu]

Hi,

of course Ken's solution is simple and useful and I would take that approach 9 out of 10 times, but just in case you really do want to evaluate the result itself:

=IF(ISNA(ERROR.TYPE("Your Formula")),"Your Formula",IF(ERROR.TYPE("Your Formula")=2,0,"Your Formula"))

Nice thing with this is that you can test for any type of error. There's a list of numbers for the errors in the help on the ERROR.TYPE formula.

Cheers,

Roel

 

[firefytr]

Also have a look at the MAX function for your original formula ..

=A1/MAX(1,A2)

Of course, Ken is wright (hehe, dumb joke, I know ;-) ) in the fact that you should deal with the source of the problem. This may not be necessary or even plausible in this case, but something to keep in mind.

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a Microsoft MVP?
- Leonardo da Vinci

 

[KenWright]

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[dhulbert]

alternatively test the cell for the the problem 0
=if( cell =0, 0 , (your formula))



I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[firefytr]

Looks familiar...

=IF(A2=0,0,A1/A2)

;-)

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a Microsoft MVP?
- Leonardo da Vinci

 

[KenWright]

LOL - funnily enough, just what I was thinking

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------