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
Check out the Formula Handbook template!