Project Health Formula

Question

Hi good afternoon, Community Members, I need some help please. I have an issue with my Health Formula. Here are some of the concerns: * I have Red tasks, but they do not affect the timeline. * Actual completion is ahead of Expected %, but Health is Red. * An activity is Yellow when it should be Green. * One delayed task makes the project Red. * Completed activities remain Red. * A new activity has no baseline dates. Project Managers are not allowed to reset the baseline unless the change is material. However, the formula does not account for this. These are the formulas I currently have. I would like some guidance so that I can achieve somewhat realistic Schedule Health Reporting. Schedule Health =IF(OR(ISBLANK([% Complete]@row), ISBLANK([Expected Progress %]@row), ISBLANK(Variance@row), ISBLANK([Baseline Start]@row), ISBLANK([Baseline Finish]@row)), "", IF([% Complete]@row = 1, IF(Variance@row >= 0, "Green", IF([Baseline Finish]@row = [Baseline Start]@row, "Green", IF(ABS(Variance@row) / ([Baseline Finish]@row - [Baseline Start]@row) > 0.05, "Red", "Yellow"))), IF(ROUND([Expected Progress %]@row, 2) = 1, "Red", IF(ROUND([Expected Progress %]@row, 2) <= ROUND([% Complete]@row, 2), "Green", IF(Variance@row < 0, "Red", "Green"))))) Expected Progress % =IF(OR(ISBLANK([Baseline Start]@row), ISBLANK([Baseline Finish]@row)), "", IF(TODAY() <= [Baseline Start]@row, 0, IF(TODAY() >= [Baseline Finish]@row, 1, IF(NETWORKDAYS([Baseline Start]@row, [Baseline Finish]@row) - 1 <= 0, 1, MIN(1, (NETWORKDAYS([Baseline Start]@row, TODAY()) - 1) / (NETWORKDAYS([Baseline Start]@row, [Baseline Finish]@row) - 1))))))

Paul Newcome · Answer

To make sure I understand…

Green is greater than or equal to expected % Complete.

Yellow is less than expected % Complete but greater than -15% expected % Complete.

Red is less than -15% expected % Complete.

I'm not sure I follow your point about not penalizing certain tasks.

Sharmilla Younglao · Answer

Hi Paul,

Thanks for your response.

Red for if the task is overdue, Yellow if the task is at risk and Green if the task is on track. Small shortfalls become Yellow; Red only after a 15-point gap.

I really just need a simple calculation that will not penalise the task if the start was 01 Sept and end 08 Sept. What would happen based on the calculation for the Expected Progress % if it indicated that the task should be at 80% but the work is at 60% it will turn red and the task is not at risk or on the critical path. Sometimes in situation like this the work is made up and the activity is completed within the time frame.

Thank you,

Sharmilla

Paul Newcome · Answer

What is the logic for each of the colors that reflect how you want it to function?