Automating RGB Balls from value scale

Kelly Lawler
Kelly Lawler ✭✭
edited 12/09/19 in Formulas and Functions



I am trying to automate into a status column a stop light symbol that reflects a value in another column that is on a scale - I am not sure what I am doing wrong...

=IF([Health Score]12>= 0, [Health Score]12 < =1.5), [Status]12, "Red", IF([Health Score]12 >1.5, [Health Score]12 <=2.9),[Status]12, "Yellow", IF([Health Score]12>3.0[Status]12,"Green","-")))


0-1.5    Red

1.5-2.9    Yellow

3.0 - 4    Green

Engagement Not Yet Started    Blue


Thank you!


  • Jgrinde27711
    edited 08/08/18

    I'm not sure if this example will help, this is what I use in my sheet, and works good, you might be able to modify it to your needs. 

    =IF(ISBLANK([Est. Approval Completion Date]1), "", IF([Est. Approval Completion Date]1 > TODAY() + 1, "Green", IF([Est. Approval Completion Date]1 >= TODAY(), "Yellow", IF([Est. Approval Completion Date]1 < TODAY(), "Red", IF([Drawing completed]1 = 1, "Grey", "")))))

  • Try this 

    =IF([Health Score]12 <= 1.5, "Red", IF([Health Score]12 <= 2.9, "Yellow", IF([Health Score]12 >= 3, "Green", "-")))


  • THANK YOU!!!! That I am trying to include a Blue ball for engagements that have not started, can you see what I am doing wrong . =IF([Health Score]13 <= 1.5, "Red", IF([Health Score]13 <= 2.9, "Yellow", IF([Health Score]13 >= 3, "Green", IF([Health Score]13 = 0, "Blue", "-"))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!