Red Yellow Green Formula based on Number Value
![Hawk23](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
Hi,
I am seeking help with a formula to assign a red, yellow, or green status to a cell based on a numerical value in the row. The hidden column name is "Difference" and it is the difference between target vs. actual units. I tried but its not working for me the way I need.
I would like Green if the difference is under 3; Yellow if the difference is between 4-8; and Red for 9 and above.
Below is what I tried, but on a line where the "Difference" value shows a negative 24, it put a green dot. Negative 24 would indicate that the provider is below their target by 24, which should be red.
Thank you in advance!
=IF(Difference@row < 3, "Green", IF(Difference@row < 6, "Yellow", IF(Difference@row >= 10, "Red")))
Best Answer
-
Try getting the ABSOLUTE value of your difference
IF(ABS(Difference@row) < 3, "Green", IF(ABS(Difference@row) < 6, "Yellow", IF(ABS(Difference@row) >= 10, "Red")))
That way, the Difference value will always be calculated from it's Positive value.
Hope that helps!
Answers
-
Try getting the ABSOLUTE value of your difference
IF(ABS(Difference@row) < 3, "Green", IF(ABS(Difference@row) < 6, "Yellow", IF(ABS(Difference@row) >= 10, "Red")))
That way, the Difference value will always be calculated from it's Positive value.
Hope that helps!
-
Thank you so much! That worked!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!