AGM ✭✭
edited 06/16/22 in Formulas and Functions

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
    Mike TV ✭✭✭✭✭✭


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


    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!