Help on creating formula for % complete, weighted on children

George Field

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?

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 🤞