When dividing by 0 can i show a result of 0 instead of #DIV/0

D

daviddm

I have a formula that divides the quantity in one cell by the quantity in
another cell to give me a result in a third cell. It's designed to give me
labor rates. The problem is if I have a particular item I don't need the rate
for it I type in 0. That give's me a#DIV/0 result. Is there a condition I can
use if my labor rate is 0 to return 0 as the result. The #div/0 screws up all
the other quantities I need.
 
F

FSt1

hi
use the iserror to test of the error.
=IF(ISERROR(B3/C3),0,B3/C3)

adjust the cell reference to suit your data.

Regards
FSt1
 
P

Per Jessen

Hi

You can replace the error like this, where A1/A2 is substituted with your
formula:

=IF(ISERROR(A1/A2);0;A1/A2)

regards,
Per
 
M

macropod

Hi daviddm,

Any number divided by 0 is infinite. Basic math. If you don't want a #DIV/0 result for some items, then don't divide them by 0.

If you need flexibility, you could use a formula like:
=IF(B1=0,A1,A1/B1)
where the divisor is in B1.
 
J

Jim Thomlinson

IMO that is a very poor use of iserror. While it will catch Divide by Zero it
will also catch #ref and a whole pile of others. That means that if your
spreadsheet references become invalid becuase of deleted rows, columns or
such then your formula will return 0. The correct result is not zero but
rather it is invalid and should show #ref so that you know that their is a
problem and the results are not reliable. In this case if you delete column C
your formula will return zero when in fact the value is not known as the
entire formula is now invalid.

IMO a better options is to check the value of the denominator and if that is
zero then return zero...

=IF(C3=0,0,B3/C3)
 
D

Dana DeLouis

if my labor rate is 0 to return 0 as the result.

Just to add another option (Excel 2007)

=IFERROR(A1/B1, 0)

= = = = = = =
Dana DeLouis
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top