RYG Formula Based on number
Hello Community;
I am having trouble writing a formula to monitor the status of a project using the RYG buttons; so far l have been able to make it work for the Red and Green button, but l need it to reflect the Yellow button as well:
Less than 10 days to MFB = Red
between 11 - 24 = Yellow
More than 25 days = Green
How can l approach this??
Best Answers
-
If anything other than those two criterion would count as yellow, you could just add, "Yellow" after the green.
=IF([Countdown to MFB]1 < 10 , "Red", IF([Countdown to MFB]1 > 25, "Green", "Yellow"))
The only challenge I see is if your countdown goes below 0 then you're going to have a red status. is that what you want? When it hits negative days?
-
Move the part about blue in front of the part about red, like this. An IF formula always fires the first matching condition it can find. Since -3 is less than 10 it will fire red.
=IIF([Countdown to MFB]5 < 0, "Blue", IF([Countdown to MFB]5 < 10, "Red", IF([Countdown to MFB]5 > 25, "Green", "Yellow")))
Answers
-
If anything other than those two criterion would count as yellow, you could just add, "Yellow" after the green.
=IF([Countdown to MFB]1 < 10 , "Red", IF([Countdown to MFB]1 > 25, "Green", "Yellow"))
The only challenge I see is if your countdown goes below 0 then you're going to have a red status. is that what you want? When it hits negative days?
-
Thanks @Mike Wilday
I hadn't gotten to that point, maybe have the button turn blue? Would that look like:
=IF([Countdown to MFB]5 < 10, "Red", IF([Countdown to MFB]5 > 25, "Green", IF([Countdown to MFB]5 < 0, "Blue", "Yellow")))
-
Yep that looks right to me. 👍️
-
Let me know if it works!
-
-
Move the part about blue in front of the part about red, like this. An IF formula always fires the first matching condition it can find. Since -3 is less than 10 it will fire red.
=IIF([Countdown to MFB]5 < 0, "Blue", IF([Countdown to MFB]5 < 10, "Red", IF([Countdown to MFB]5 > 25, "Green", "Yellow")))
-
ETA; Updated to the correct name of blue lights and still not working:
-
@Mike Wilday It works! ☺
-
Awesome! Glad I could help you out! :) Smartsheet always processes IF statements from left to right and stops whenever it finds the first TRUE criterion.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.3K Get Help
- 386 Global Discussions
- 212 Industry Talk
- 446 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 292 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!