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

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

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
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 200 Industry Talk
 429 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!