Change RYG Automatically based on values

afineline

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.

Hello,

 

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.