Need help with formula to alert when Actual Progress is behind Projected Progress

Ashley Johnson

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!

Paul Newcome

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%).

Paul Newcome

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?

Ashley Johnson

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

Paul Newcome

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%).

Ashley Johnson

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

Paul Newcome

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.

Gareth Attwood

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