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

Run Project

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

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

Suman Kumar Jha

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.

Hi @Suman Kumar Jha

Try using AVG instead of AVERAGE, see: AVG Function

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

Suman Kumar Jha

Thank you, that works!