Trouble with RYG Formula:

Beth Blome

Can anyone tell me what I am doing wrong here?  I am trying to automate RYG balls from a % column.

=IF([% Complete]3 < 50, "Red", IF([% Comlete]3 = 100, "Green, IF([% Complete] >50 AND <100, "Yellow")

Beth Blome

=IF([% Complete]3 < 50, "Red", IF([% Complete]3 = 100, "Green, IF([% Complete] >50 AND <100, "Yellow")

 

Corrected spelling error!

 

Mike Wilday

Smart sheet displays the percentages as such, but in reality the backend is reading a decimal. When referencing perecentages in formulas you should use .5 for 50% and 1 for 100%. SO your formula would look like this... 

=IF([% Complete]3 < .5, "Red", IF([% Complete]3 = 1, "Green, IF([% Complete] >.5 AND <1, "Yellow")

Beth Blome

Thanks Mike, but there is still something wrong:  unparseable!

I have tried a dozen times with the same results!

 

 

 

Mike Wilday

Duh I should have looked more carefully!  And statements should be made as indicated below... 

=IF([% Complete]3 < .5, "Red", IF([% Complete]3 = 1, "Green, IF(AND([% Complete]3 >.5, [% Complete]3 <1), "Yellow")))

You also didn't have enough closing parenthesis. 

Beth Blome

Maybe I have been staring at this too long!  It still doesn't work.  Did it work for you?

 

YOU are a peach, by the way.  Thanks for helping.

Mike Wilday

Are you getting unparseable still? 

Mike Wilday

=IF([% Complete]3 < .5, "Red", IF([% Complete]3 = 1, "Green", IF(AND([% Complete]3 >.5, [% Complete]3 <1), "Yellow")))

Green was missing a closing quotation mark!

Beth Blome

Yes

#baldfromtearinghairout

Mike Wilday

Did this one work? 

=IF([% Complete]3 < .5, "Red", IF([% Complete]3 = 1, "Green", IF(AND([% Complete]3 >.5, [% Complete]3 <1), "Yellow")))

Green was missing a quotation mark!