Hi, I would love some help smoothing out my whatif formula around task health

caparker17

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!

Paul Newcome

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