Need help with % complete based on duration without using dependencies

Liz Wallace

I have a sheet that I do not want to use dependencies on, so I can cell link dates. That being said I want to calculate duration and % complete based off that duration for parent rows

My duration equation is: =NETWORKDAYS([Start Date]@row, [End Date]@row)

But I cannot figure out how to calculate % complete so that the parent and grandparent rows roll up the right percentage and ultimately give me a total % complete (like you get with dependencies enabled)

Matt Lynn-PCG

@Liz Wallace It won't work exactly like using preds but the common way is to put this formula in the parent rows:

=avg(children())

The better way (IF you're calculating the % complete… see below update) would be to convert that to a column formula. To do this you would want to add a helper column (hidden) to calculate if it's a child or parent etc. This formula would go in the new helper column just named "C".

=count(children())

Then in your % Complete column put:

=if(c@row>0,avg(children()), INSERT YOUR EXISTING % COMPLETE FORMULA HERE )

Then make that a column formula.

UPDATE:

Note… my comment about the % complete being a column formula was based on your existing children rows being a formula to calculate the difference in today's date in relation to the start/end dates… this is referred to a "period of performance" BUT…. if you want to enter that manually then just to the =avg(children()) formula on the parents and leave the children manual text.

Sam_Harwart

How precise do you need it to be? Typically the parent's % complete is a weighted average of the durations and % complete from the children, but you could make it a straight average:

Something like this:

=AVG(CHILDREN([% Complete]@row))