% Completion Calculation Parent and Child

abouit

I'm have project smartsheet that I'm trying to figure out the formula for calculating parent % complete base on sub child completion percentages.

Parent % Completed

Child 1 % Completed

Sub Child 1 % Completed

Sub Child 2 % Completed

Child 2 % Completed

Sub Child 1 % Completed

Sub Child 2 % Completed

What would be the formula to automatically calculate Child 1 % complete based on sub child % complete and Parent % complete based on Child % completion numbers? I'm not using the Project Dependency functionality.

Thanks

Alain

Michael Pappas

If you want all of the children/sub children weighted the same, you could use

=SUM(CHILDREN())/COUNT(CHILDREN())

Michael Pappas

If you want all of the children/sub children weighted the same, you could use

=SUM(CHILDREN())/COUNT(CHILDREN())

abouit

Is there a weighted version of this?

Alain

Michael Pappas

If you have a duration column, then you could probably use the % of the total duration to weight it.

Basically, if Sub Child 1 takes 5 days and Sub Child 2 takes 10 days, but start at the same time, the total duration is 10 days. After 5 days, if Sub Child 1 and 2 are 100% and 50% complete, respectively, the formula above will show you 75% complete. You could weight the % complete to weight Sub Child 2 heavier than Sub Child 1 since it takes twice as long. The assumption being that double the duration = double the work.

Then, if Sub Child 1 was 100% complete, but Sub Child 2 was 0% complete, Child 1 would be 33% complete instead of 50% complete since only 5 of the 15 total days of work has been completed.

The Parent and Child row durations need to be =SUM(CHILDREN()).

Then, I believe that you would need to create a helper column with this formula

=Duration@row / PARENT(Duration@row)

Then, the formula in your Parent and Child rows would be:

=AVGW(CHILDREN(), CHILDREN(Weight@row)) * SUM(CHILDREN(Weight@row))