Hi, I would love some help smoothing out my whatif formula around task health
I am on a complex software development program with multiple releases being managed. I've created reports, summary sheets, and dasboards to manage this. Typically im fine with the formulas but this one has stumped me for more than 6 hours in total so i wanted to run it by this team of experts.
I need to run a task summary (In Risk, On Schedule) against all tasks and milestones that takes into consideration duration, percentage complete, and end date. Based on total task duration, we essentially want progress to run in parallel. So once 25% of the total duration to be reached, we want 25% of that task done. 50% duration to 50% complete and 75% to 75%. If it meets or excends then it is on schedule, it it is less then at risk. If start date is in the future then it can on schedule and if end date is in the past then it can be at risk.
The formula I've worked out so far is:
=IF(OR([% Complete]@row = 1, [Start Date]@row > TODAY()), "On Track", IF([End Date]@row < TODAY(), "At Risk", IF([End Date]@row <= TODAY(5), IF([% Complete]@row <= 0.75, "At Risk", IF([% Complete]@row >= 0.75, "On Schedule")), IF([End Date]@row <= TODAY(10), IF([% Complete]@row >= 0.5, "On Schedule", IF([% Complete]@row <= 0.5, "At Risk", IF([End Date]@row >= TODAY(10), IF([% Complete]@row >= 0.25, "On Schedule", IF([% Complete]@row <= 0.25, "At Risk")))))))))
I've been using generic 5/10+ days to start with but I've created columns for .25/.50/.75 of duration time i would want to include so that both large and small tasks can be properly included in this metric.
Here is a snip of my plan with Status Health being the column that Im using for this KPI. Any recommendations or assistance would be of huge help. Thank you so much!
Answers
-
We can actually calculate the expected percent complete based on today's date in correlation to the start and end dates and then compare that to the actual percent complete.
Then we can say that if the actual meets or exceeds projected or if the start date is in the future, "On Schedule", otherwise "At Risk".
Expected % Complete (capped at 100%):
=MIN(1, (TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row))
Nesting that in the IF...
=IF(OR([Start Date]@row> TODAY(), [% Complete]@row>= expected_%_complete), "On Schedule", "At Risk")
=IF(OR([Start Date]@row> TODAY(), [% Complete]@row>= MIN(1, (TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row))), "On Schedule", "At Risk")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!