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

Options

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.

• ✭✭✭✭✭
Options

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

• edited 10/18/22
Options

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)

• Employee
Options

Try using AVG instead of AVERAGE, see: AVG Function

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