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 48; 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
Check out the Formula Handbook template!