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?

=如果([% /下(+)(-)]5 > 0,“绿色”,如果([%机汇ER (-) 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:

=如果([% /下(+)(-)]5 > 0,“绿色”,如果([%机汇ER (-) 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.

afineline

Thank you!