Need help with formula to alert when Actual Progress is behind Projected Progress
I have been trying to nail this down for the past couple of weeks but falling short. I need a column that shows Red, Green, or Yellow based on Actual % Complete vs Projected % Complete. I am calling it Progress Alert. I need it to show the following:
If Actual % Complete is 25%+ behind Projected % Complete show Red
If Actual % Complete is 15%+ behind Projected % Complete show Yellow
If Actual % Complete is 10% or less behind Projected % show Green
If Actual % Complete is ahead of Projected % at all show Green
Background- I currently have the Progress Alert working with the Progress column Harvey balls but the last criteria listed above doesn't work. If the Progress is ahead of Projected % Complete the Progress Alert is showing Red which is our main issue. I added an Actual % Complete which converts the Harvey balls to % hoping that may make it easier to do the formula. The Projected Complete is calculated based on the days on the Planned Dates.
Any help would be much appreciated!
Help Article Resources
Check out the Formula Handbook template!