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?
Help Article Resources
Check out the Formula Handbook template!