Creating RYG based on differences between two percentage columns
I have a percentage planned and a percentage complete column in a project sheet and want to acheive the below:
Highlight Red ball when % complete is more than 5% below % planned.
Highlight Yellow ball when % complete is between 5 & 1% below % planned.
Highlight Green ball when % complete is >= % planned.
Appreciate any guidance!
Answers
-
Try something like this:
=IF(Planned@row ⇐ Actual@row, “Green”, IF(Planned@row ⇐ Actual@row + 0.05, “Yellow”, “Red”))
It seems as though there are some formatting issues here in the community forum at the moment. You are going to need to retype the formula directly in Smartsheet (do not copy/paste from here) to get the proper quotes and replace each ⇐ with an < followed immediately by an = for “less than or equal to”.
-
Thanks Paul,
That worked for me. I used this formula =IF([% Planned]@row <= [% Complete]@row, "Green", IF([% Planned]@row <= [% Complete]@row + 0.05, "Yellow", "Red"))
Can I add a stipulation that if End date column is greater than today and % planned is not 100% the Red ball is highlighted?
Thanks,
-
Do you mean End Date is in the past?
-
Hi Paul, I made no sense in that last message.
Yes, if end date is in the past and % complete is less than 100%, the red ball is highlighted.
Thanks,
-
Give this a go...
=IF(AND([End Date]@row< TODAY(), [% Complete]@row <> 1), "Red", IF([% Planned]@row <= [% Complete]@row, "Green", IF([% Planned]@row <= [% Complete]@row + 0.05, "Yellow", "Red")))
-
Hi Paul,
Got it sorted in the meantime with
=IF(AND([% Complete]@row < [% Planned]@row, Finish@row < TODAY()), "Red", IF([% Planned]@row <= [% Complete]@row, "Green", IF([% Planned]@row <= [% Complete]@row + 0.03, "Yellow", "Red")))
Thanks for your help
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!