When dividing by 0 can i show a result of 0 instead of #DIV/0
in [General]
|
From: Jim Thomlinson on 23 Dec 2009 16:29 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.
From: Teethless mama on 23 Dec 2009 16:41 =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.
From: Dana DeLouis on 27 Dec 2009 14:29
> 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. |