Red Yellow Green Formula based on Number Value



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

  • francis.naud
    francis.naud ✭✭
    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!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!