# Need help with % complete based on duration without using dependencies

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)

• ✭✭✭✭✭✭
edited 08/26/24

@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.

Certified Platinum Partner

2023 Partner of the Year

PrimeConsulting.com

• ✭✭✭✭✭

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))

Thanks,
Sam

Want to chat about a Smartsheet problem you're facing? Grab time on my calendar here: Schedule a Discovery Call!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!