When dividing by 0 can i show a result of 0 instead of #DIV/0
in [General]
|
From: daviddm on 23 Dec 2009 16:10 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.
From: FSt1 on 23 Dec 2009 16:18 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.
From: Per Jessen on 23 Dec 2009 16:20 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" <daviddm(a)discussions.microsoft.com> skrev i meddelelsen news:54976670-B7B2-42E1-8E03-29BE5C4EDF36(a)microsoft.com... >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.
From: FSt1 on 23 Dec 2009 16:21 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.
From: macropod on 23 Dec 2009 16:23
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" <daviddm(a)discussions.microsoft.com> wrote in message news:54976670-B7B2-42E1-8E03-29BE5C4EDF36(a)microsoft.com... >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. |