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!

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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%).


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!