% Complete Average Column Formula

Rachel Gadzicki

I am currently using a Column Formula to calculate % Complete. The formula is a combination of two checks:

1. 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.
2. 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!

Mark Cronk

Hi @Rachel Gadzicki ,

What is you used =SUM(CHILDREN()) / COUNT(CHILDREN()) in the % complete cell of the project name row? It won't bring the 33% up but it will average the children and should represent the % complete if you assume each child is equal.

Curious to see what other answers surface. Good luck.

Mark

Rachel Gadzicki

Hi @Mark C. ,

This is exactly what I'm looking for, and it calculated as expected when I added it it to the Project Name row!

Here's what I have now:

Topmost Row: =SUM(CHILDREN()) / COUNT(CHILDREN())

Child Rows: =IF(COUNT(CHILDREN(Complete@row)) > 0, (COUNTIF(CHILDREN(Complete@row), 1) / COUNT(CHILDREN(Complete@row))), IF(Complete@row, 1, 0))

Last question, is there any way to combine this so I can make it a column formula and always have the topmost row calculate like it's the separate formula? I know I can drag the child row formula down the entire column, just curious if I could set it to a column formula.

Mark Cronk

Good morning @Rachel Gadzicki ,

Unfortunately this solution doesn't allow you to use a column formula. A column formula requires the same formula in all rows.

As a work around, you could move the project name calculation to a new column and use =SUM(CHILDREN([%Complete]@row)) / COUNT(CHILDREN([% Complete]@row)). Then convert your % complete column to a column formula with your child row formula. The project name value in the % complete row will be wrong. You can't change the formula but you can make the text white so it isn't visible.

Good luck.

Mark