RYG formula based on % Complete

Deb Gordinier

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 = 50-99%

Red is = 0-50%

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!

Mike Wilday

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.  

Deb Gordinier

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!! 

Mike Wilday

I'm glad I could assist! Happy Smartsheeting!