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
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!