#DIV/0! to read as 0

[Guest]

Is there a simple formula for displaying "0" as a result, not "#DIV/0!", when
cells are intentionally left blank or contain "0" as a divisor ?
If so, how is it applied with a formula already in the cell?
Hope someone's got the answer..
thanks

 

[joeu2004]

Is there a simple formula for displaying "0" as a result, not "#DIV/0!", when
cells are intentionally left blank or contain "0" as a divisor ?
If so, how is it applied with a formula already in the cell?

Usually, the best way is to simply test the divisor. If you know D1
can be only blank or a number, you can do the following:

=if(D1=0, 0, A1/D1)

If the divisor is an expression, you might need to test it. For
example:

=if(D1+D2=0, 0, A1/(D1+D2))

If the divisor might contain a non-number, you might want to do
something like the following:

=if(N(D1)=0, 0, A1/D1)

 

[Guest]

you can use an iserror such as
=IF(ISERROR(1/0),"",1/0)

 

[Guest]

If you have a formula like =A1/B1 returning an unwanted #DIV/0! error then to
show zero instead

=IF(B1,A1/B1,0)

 

[JE McGimpsey]

While this will work to display "" rather than #DIV/0 (though the OP
wanted to display 0), it will also cause any other errors to fail
silently.

Better to test the divisor:

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

 

[Chip Pearson]

Just for the record, in Excel 2007 you can use the new IFERROR function.
E.g.,

=IFERROR(x/y,"")

It will return x/y is no error occurs, or an empty string if an error
occurs.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[joeu2004]

Just for the record, in Excel 2007 you can use the new IFERROR function.
E.g.,
=IFERROR(x/y,"")
It will return x/y is no error occurs, or an empty string if an error occurs.

Kudos to Bill's Kids for finally recognizing the need for this. It
avoids evaluating every expression twice (klunk!).

Now, did they also increase the nested function depth to at least 8 ;-).

 

[Chip Pearson]

Now, did they also increase the nested function depth to at least 8 ;-).

Yes, they did. The limit now is, I think, 64 nested functions. I would never
want to try to debug a formula with 64 levels of parens, but you can now
write such a function. Sixty-four parens is

))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

It would be no small effort to try to match up opening and closing parens.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Dana DeLouis]

Now, did they also increase the nested function depth to at least 8 ;-).

Here's some interesting late night reading...

Improving Performance in Excel 2007
http://msdn2.microsoft.com/en-us/library/aa730921.aspx