Need help with formula to alert when Actual Progress is behind Projected Progress
Hello
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!
Best Answer

Try this...
=IF([Projected % Complete]@row  .25>= [Actual % Complete]@row, "Red", IF([Projected % Complete]@row  .15>= [Actual % Complete]@row, "Yellow", "Green"))
Please note: There is a gap in your logic between Yellow and Green. Yellow is more than 15% but Green is less than 10%. Writing a formula to match this exactly would leave a blank for any row between those two differences. The formula above shows Yellow for more than 15% and Green for less than 15% (instead of less than 10%).
Answers

Exactly how are your percentages entered? If through formulas, can you provide those formulas? If through manual entry, are you manually keying the % or are you just entering a number into a text/number column that has been formatted for percentages?

Actual % Complete =IF(Progress@row = "Empty", 0, IF(Progress@row = "Quarter", 0.25, IF(Progress@row = "Half", 0.5, IF(Progress@row = "Three Quarter", 0.75, 1))))
Projected % Complete =IF(TODAY() < [Planned START]@row, 0, IF(TODAY() > [Planned END]@row, 1, (TODAY()  [Planned START]@row) / ([Planned END]@row  [Planned START]@row)))

Try this...
=IF([Projected % Complete]@row  .25>= [Actual % Complete]@row, "Red", IF([Projected % Complete]@row  .15>= [Actual % Complete]@row, "Yellow", "Green"))
Please note: There is a gap in your logic between Yellow and Green. Yellow is more than 15% but Green is less than 10%. Writing a formula to match this exactly would leave a blank for any row between those two differences. The formula above shows Yellow for more than 15% and Green for less than 15% (instead of less than 10%).

Thank you, Paul! This is what I have needed for weeks!

Happy to help. 👍️
The Community is always a great place to come if you ever hit a roadblock! There are a lot of knowledgeable people wandering around here.

Hi, this is really useful, Id like to know how to add the Blue as showing that the task is complete: can you advise?
Thanks
Help Article Resources
Categories
Check out the Formula Handbook template!