% 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!

Find more posts tagged with

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.