Formula for R,Y, G Harvey Balls

Jennifer Vaughn
edited 12/09/19 in Smartsheet Basics

I am trying to generate a formula that will turn the Harvey Balls in a cell to the color of their matching steps. For example, I have 21 steps all numbered 1 thru 21. I want the HB to be green if the steps are greater than or equal to 16. I want the HB to be yellow if the steps are greater than 11 but less than 15. And red if the steps are greater than or equal to 11. I have tried several formulas and end up with "unparseable" on all:

=IF([Steps]17 >= 16, “Green”, IF(AND([Steps]17 > 11, [Steps]17 < 15), “Yellow”, IF([Steps]17 <= 11), “Red”)))))

=IF(And(Steps for Harvey Balls19 > 0, (Steps for Harvey Balls19 < 11), "Red", IF(AND(Steps for Harvey Balls19 > 16,(Steps for Harvey Balls19 = 16), "Green", IF(AND(Steps for Harvey Balls19 > 11, (Steps for Harvey Balls19 < 15), "Yellow"))))

 

These are two that I used from other examples but neither work. The first one seems the most right. I think it may be a ( or ] out of place?? Any suggestions on how to fix my formula? Thanks. 

 

Comments