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
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.
Comments
-
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.
-
Thank you!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives