RYG Formula Based on number

Silvia Rangel

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

Mike Wilday

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?

Mike Wilday

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

Mike Wilday

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?

Silvia Rangel

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

Mike Wilday

Yep that looks right to me. 👍️

Mike Wilday

Let me know if it works!

Silvia Rangel

@Mike Wilday it did not work, l am not getting an error but the button stays Red:

Thanks!

Mike Wilday

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

Silvia Rangel

ETA; Updated to the correct name of blue lights and still not working:

Silvia Rangel

@Mike Wilday It works! ☺

Mike Wilday

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.