"#DIVIDE BY ZERO" Error

AGM

I am utilizing the following formula: =[Defects Complete]@row / [Column7]@row

However, both my numerator and denominator are "0". How do I account for this so that I still display 100% in this cell?

Please help!

Mike TV

@AGM

=IF(AND([Defects Complete]@row = 0, [Column7]@row = 0), 1, [Defects Complete]@row / [Column7]@row)

You could use an IFERROR formula but I wanted to do it this way so that if you're getting a different error than DIVIDE BY ZERO that it would show the error still instead of just going with 100% for any error at all.

Jeff Reisman

@AGM There are a few ways to do this.

To convert any error to a different value, wrap your formula in IFERROR:

=IFERROR([Defects Complete]@row / [Column7]@row, 1)

This says if the formula results in an error, set the value of the cell to 1.

(If the above is in a Percent-formatted column, use 1 at the end - it will show up as 100% - but if it's just a regular text/number column and you don't need to do calculations on it, use "100%")

You could also use an IF statement around your formula:

=IF(AND([Column7]@row = 0, [Defects Complete]@row = 0), 1, ([Defects Complete]@row / [Column7]@row))

This says if the Column7 value and Defects Complete value are both 0, set the value to 1, otherwise, do the math.

(Same caveat regarding percent-format as above.)