Expected % Complete not match automated % Complete Parent Rows Formula
I am attempting to do a Expected % complete and I cannot determine how to align my parent rows calculation to match the automated % complete calculation within Smartsheet.
Currently all children row formulas are the following:
=ROUND(IF([Estimated Start Date]@row < TODAY(), MIN((TODAY() - [Estimated Start Date]@row) / ([Estimated Finish Date]@row - [Estimated Start Date]@row), 1), 0), 2)
And whether I use the same formula for parent rows or an AVG the calculations do not align with the automated % complete parent rows:
Parent Rows as an average:
=AVG(CHILDREN())
Parent row as:
=ROUND(IF([Estimated Start Date]@row < TODAY(), MIN((TODAY() - [Estimated Start Date]@row) / ([Estimated Finish Date]@row - [Estimated Start Date]@row), 1), 0), 2)
I am curious how Smartsheet's calculates parent rows for % complete so I can attempt to match it.
Answers
-
It's because it's a weighted % complete, not an average.
Smartsheet takes the % complete of a row and applies it to the duration to arrive at a number of days that are completed.
It does that for all the rows, then sums the total number of completed days. Finally it takes that total completed days and compares to the rolled up Start and Finish dates (earliest and latest) to arrive at the % complete for the parent.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!