# Red Yellow Green Formula based on Number Value

Options
✭✭

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.

=IF(Difference@row < 3, "Green", IF(Difference@row < 6, "Yellow", IF(Difference@row >= 10, "Red")))

Options

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!

Options

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!

• ✭✭
Options

Thank you so much! That worked!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!