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!
Help Article Resources
Check out the Formula Handbook template!