#DIV/0! to read as 0

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 wrote:
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)

you can use an iserror such as
=IF(ISERROR(1/0),"",1/0)
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"PATTO" wrote:

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

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

=IF(B1,A1/B1,0)

"John Bundy" wrote:

you can use an iserror such as
=IF(ISERROR(1/0),"",1/0)
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"PATTO" wrote:

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

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)

In article ,
John Bundy (remove) wrote:

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

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)


"John Bundy" (remove) wrote in message
news
you can use an iserror such as
=IF(ISERROR(1/0),"",1/0)
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"PATTO" wrote:

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

Chip Pearson wrote:
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 ;-).

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)


wrote in message
oups.com...
Chip Pearson wrote:
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 ;-).

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

--
HTH :)
Dana DeLouis
Windows XP & Office 2003


wrote in message
oups.com...
Chip Pearson wrote:
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 ;-).