Formula for R,Y, G Harvey Balls

Jennifer Vaughn

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. 

 

Mike Wilday

Try the first one and remove the brackets. If your column name is only one word you don't need the brackets. It also looks like you had too many closing parentheses. 

=IF(Steps17 >= 16, “Green”, IF(AND(Steps17 > 11, Steps17 < 15), “Yellow”, IF(Steps17 <= 11), “Red”)))

Let me know if that works. 

rjudenberg

your description and your examples don't quite match, particularly around the boundary cases.  Below code is yellow from 12-15, red below, green above.

=IF(Steps1 >= 16, "Green", IF(Steps1 > 11, "Yellow", "Red"))

Jennifer Vaughn

This one still came back as "unparseable" Thanks. The below solution did work though.

 

Jennifer Vaughn

This one worked. Thank you very much!!

Mike Wilday

Cool. Glad you got it working.