Red Yellow Green Formula based on Number Value
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
- 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
Check out the Formula Handbook template!