Either checkbox column to contribute to % complete

Rachel Kelly

I am looking for a formula that will reference two checkbox columns to calculate a % complete if either is checked. I'd also like this formula to calculate an average % complete based on the % complete of the child tasks. This was working correctly when I was using only one checkbox column, but I am struggling to incorporate the second column into the formula.

Here was the correct function that only looked to the Done column.

=IF(COUNT(CHILDREN(Done@row)) > 0, (COUNTIF(CHILDREN(Done@row), 1) / COUNT(CHILDREN(Done@row))), IF(Done@row, 1, 0))

Here's what I'd like to do:

If the row is a parent row, calculate the average % complete of it's children. If the row is a child row, calculate 100% complete if Done is checked or N/A is checked.

Here is the formula I am currently using, I know the OR function is incorrect in some way.

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, OR((COUNTIF(CHILDREN(Done@row), 1) / COUNT(CHILDREN(Done@row))), (COUNTIF(CHILDREN([N/A]@row), 1) / COUNT(CHILDREN([N/A]@row))))), IF(OR(Done@row = 1, [N/A]@row = 1), 1, 0)

Any advice on how to get this functional?

Paul Newcome

Try this:

=IF(COUNT(CHILDREN()) = 0, IF(OR(Done@row = 1, [N/A]@row = 1), 1, 0), AVG(CHILDREN()))

Paul Newcome

Try this:

=IF(COUNT(CHILDREN()) = 0, IF(OR(Done@row = 1, [N/A]@row = 1), 1, 0), AVG(CHILDREN()))

Rachel Kelly

Thank you @Paul Newcome! That worked!

Paul Newcome

Happy to help. 👍️