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.

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

"daviddm" wrote:

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.

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

"daviddm" skrev i meddelelsen
...
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.

another way
=if(C3=0,0,B3/C3)

either way works

regards
FSt1

"FSt1" wrote:

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

"daviddm" wrote:

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.

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.

--
Cheers
macropod
[Microsoft MVP - Word]


"daviddm" wrote in message ...
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.

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)
--
HTH...

Jim Thomlinson


"FSt1" wrote:

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

"daviddm" wrote:

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.

=IF(C3,B3/C3,)


"daviddm" wrote:

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.

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

On 12/23/2009 4:10 PM, daviddm wrote:
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.