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?
Answers

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

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 🤞
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!