RYG formula based on % Complete
I'm trying to create a formula that will automate the RYG balls (red, yellow, green) based on the project's % Complete.
Green = 100%
Yellow = 5099%
Red is = 050%
Below is how I wrote the formula, but I'm getting a Unparseable errors.
**Note, I'm trying to enter the formula into the Status column on row #5, figuring I would autofill the formula down if I can get it to work. That Status column is already formatted for the RYG symbols.
=IF([% Complete]5 = 100%, "Green", IF([% Complete]5 > 50% AND <100) "Yellow", IF([% Complete]5 < 50%, "Red")))
Not sure if this matters, but the [% Complete] column defaults to % format. I saw some other threads that said t0 enter the percentage using 2 decimals. So I tried;
=IF([% Complete]5 = 1%, "Green", IF([% Complete]5 > .5% AND <1) "Yellow", IF([% Complete]5 < .5%, "Red")))
Same error. Then I tried removing the % signs...
=IF([% Complete]5 = 1, "Green", IF([% Complete]5 > .5 AND <1) "Yellow", IF([% Complete]5 < .5, "Red")))
Does anyone know why this isn't working?
Thanks in advance!
Comments

Yep! There are a couple reasons, try this one and see if it works for you...
=IF([% Complete]5 = 1, "Green", IF([% Complete]5 > .5, "Yellow", IF([% Complete]5 < .5, "Red")))
You were missing a comma after the >.5. You also don't need the AND portion of your yellow statment because it won't pass form the Green statement if it's 100%, anything that isn't 100% (1) will pass to the yellow so you will need to check if its bigger than .5. If it isn't it will pass to your last statement and see if it is less than 5. You could also simplify it like this...
=IF([% Complete]5 >= 1, "Green", IF([% Complete]5 > .5, "Yellow", "Red"))
Because anything that is Less than 50% is red. And anything greater than or equal to 1 (100%) is green. I added the greater than symbol in case someone gets cheeky and says its 110% complete.

Mike, you are a lifesaver!! For whatever reason, I just wasn't understanding the logic behind this formula, but you just made it all click!
I now understand both formulas and the reasons mine didn't work. Thank you so much!!

I'm glad I could assist! Happy Smartsheeting!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!