N/A Column leads to #DIVIDE BY ZERO

cmcneillyjci ✭✭✭
edited 12/09/19 in Formulas and Functions

I'm working on an onboarding tracker where I'm using an "N/A" checkbox in order not to count the modules that individuals aren't required to take.  I'm using another checkbox to represent "Done" and the percentage complete -  =COUNTIF(CHILDREN(Done16), 1) / COUNTIF(CHILDREN([N/A]16), 0)

If an entire module is chosen as "N/A" the result is #DIVIDE BY ZERO.  Is there a way to alter the formula to not divide by a number?  




  • L_123
    L_123 ✭✭✭✭✭✭

    =COUNTIF(CHILDREN(Done16), 1) / if(COUNTIF(CHILDREN([N/A]16), 0) = 0,1,COUNTIF(CHILDREN([N/A]16), 0))

    You cannot divide by zero. If there aren't any checked N/A, then your denominator is zero and you have an incorrect formula. You just need to check the case that the denominator is 0 and post a 1 instead.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!