Red Yellow Green Formula based on Number Value

Hawk23

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")))

francis.naud

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!