Help on creating formula for % complete, weighted on children

The sheet has each job recorded as a parent row with sub tasks as the children. Each task is allocated hours for the job to be completed (taken from a pool for the entire job). % complete column for the child rows is entered manually depending on how complete the job is.

Yellow highlight on screenshot shows total allowed hours for all the children (55) and % complete cell for Parent where I want to place the formula.

I want to create formula in the parent % complete cell that calculates the following

Divides each child’s allowed hours by the total hours for the job (to get the % weight of each child row individually)

Then average the % complete of all children (depending on how complete the job is for each child)

Eg. If the job has 2 child rows – Total hours for job is 20

1 child row allocated 15 hours

1 child row allocated 5 hours

% complete for one child is 100% and 0% for the other the parent cell will read 75% complete. Equally want it to average out the % complete if one is 100% and one is 50%.

Can’t figure out how to put formula together for this! Can anyone please help?

Find more posts tagged with

Accepted answers

All comments

ron.judenberg112096

Just an FYI, but if you use the %complete column functionality, it will automatically weight the children by the duration (not effort hours) column

That said,

create a column called weighted

for each child, set the formula in weighted to =[% Complete]@row * [allowed hours]@row / PARENT([allowed hours]@row)

the sum of that column is your weighted average

George Field

Thanks Ron, I have turned off the auto formula for the % complete column and are trying to add this manually as doing by the duration has not been working for us correctly.

Will test out your formula! thanks for your help 🤞

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.