% Complete Average Column Formula
I am currently using a Column Formula to calculate % Complete. The formula is a combination of two checks:
- Children rows calculate based on a check box "Complete" column. The % complete will be calculated as 0% for an unchecked box or 100% for a checked box in the Complete column.
- Parent rows are designed to calculate the average of the children rows.
Here is the formula I'm using:
=IF(COUNT(CHILDREN(Complete@row)) > 0, (COUNTIF(CHILDREN(Complete@row), 1) / COUNT(CHILDREN(Complete@row))), IF(Complete@row, 1, 0))
Unfortunately, with the way the formula is written, it is waiting to calculate an average for the grandparent until the parent is marked as complete (even when the children of a parent are calculating an average for the parent cell).
I've included the below image for reference.
How can I adjust my column formula row to calculate the true average of all children? I'd like the 33% to roll up into the grandparent cell, even though that row is not yet marked as complete. Any advice would be greatly appreciated!
Help Article Resources
Check out the Formula Handbook template!