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

Options

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!

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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?

• Options

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

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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.

• Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!