Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Change RYG Automatically based on values

edited 12/09/19 in Archived 2017 Posts

Hello – I am trying to create a formula that will change RYG automatically with the following criteria:

Green:  Any positive (%)

Yellow:  0 to negative 10%

Red:  less than negative 10%

With the below formula, green and yellow work fine but not red.  How close am I?

=IF([% OVER (-) UNDER (+)]5 > 0, "Green", IF([% OVER (-) UNDER (+)]5 <> 0 - (-0.1), "Yellow", "Red"))

Any help is appreciated.

Thank you.


  • Robert S.
    Robert S. Employee



    Thanks for the question. Your formula is very close. The reason it's not working as you'd like, is the part with "<> 0 - (-0.1)" resulting in Yellow. This expression means "is not equal to 0.1 (10%)". Meaning any value that doesn't fall into the Green portion of the formula and isn't 10% (which falls into the Green portion anyway), will become Yellow. With the criteria you'd like this to work with, the formula could look something like this:


    =IF([% OVER (-) UNDER (+)]5 > 0, "Green", IF([% OVER (-) UNDER (+)]5 < -0.1, "Red", "Yellow"))


    This will check to see if the value is greater than 0% and if so turn it Green, and if not will then check if the value is less then -10% and if so turn it Red, and for any other values it will turn Yellow. Hope this helps.

This discussion has been closed.