#DIV/0! to read as 0

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
PATTO said:
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?
Click to expand...

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)
 
If you have a formula like =A1/B1 returning an unwanted #DIV/0! error then to
show zero instead

=IF(B1,A1/B1,0)
 
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)
 
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)
 
Chip said:
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.
Click to expand...

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 ;-).
 
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)
 
Back
Top