Counting "N/A" as 100% instead of 0%

Hey everyone,

I've gone round and round on this one and at this point I think I'm just thinking about it too hard.

So for starters, how come in creating a percent complete column, if one includes an "N/A" in the dropdown, it does not automatically remove it from the calculation of the overall percentage complete? Because of this issue, it always looks as though there's still tasks to be completed when there isn't.

Well I fixed that issue with a formula: =SUM(CHILDREN()) / (COUNT(CHILDREN()) - COUNTIF(CHILDREN(), ="N/A"))

It worked, and everything was well and good. Then one day I needed to label all children in a parent task "N/A" which then messed up the calculation, giving me: #DIVIDE BY ZERO

So now I'm left with trying to find a way to count my "N/A" as 100%, so that our dashboards will reflect accurate completion of tasks.

Please help...

Answers

  • Ramzi K
    Ramzi K ✭✭✭✭✭

    @Run Project

    If your goal is to show the parent task as complete in the case where all subtasks are "N/A" then just wrap your formula in IFERROR like this:

    =IFERROR(SUM(CHILDREN()) / (COUNT(CHILDREN()) - COUNTIF(CHILDREN(), ="N/A")), 1)

    It will give you 100% in this case.

    I hope this helps.

    Cheers,

    Ramzi

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: ramzi@cedartreeconsulting.com

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

  • Suman Kumar Jha
    edited 10/18/22

    I have similar scenario where % needs to be derived from children task and in case of N/A (all tasks) it should mark this 100%. the formula modified for average is not working

    =IFERROR(AVERAGE(CHILDREN()) / (COUNT(CHILDREN()) - COUNTIF(CHILDREN(), ="N/A")), 1)

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Suman Kumar Jha

    Try using AVG instead of AVERAGE, see: AVG Function

    =IFERROR(AVG(CHILDREN()) / (COUNT(CHILDREN()) - COUNTIF(CHILDREN(), ="N/A")), 1)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!