IF AND RYG Formula
Hello, I've created the following formula and one part is not working: the RYG Red balls are not displaying when they should and instead are displaying Yellow. Can you help me identify why this is happening (my IF/AND part of the formula does not seem to be working)? Thank you!
* If the Finish (date) field is blank, the Gray ball will appear
* If the % Complete = 0, the Gray ball will appear
* If the % Complete = 1, the Green ball will appear
* If Finish (date) is < Today (+14 days), the Yellow ball will appear
* If the % Complete <1 AND the Finish (date)>TODAY, the Red ball SHOULD appear but it is displaying as Yellow
=IF(ISBLANK(Finish@row), "Gray", IF([%Complete]@row = 0, "Gray", IF([%Complete]@row = 1, "Green", IF(Finish@row > TODAY(+14), "Green", IF(Finish@row <= TODAY(+14), "Yellow", IF(AND([%Complete]@row < 1, Finish@row > TODAY(), "Red")))))))
Answers

It might be because you forgot a parentheses in the correct place for your IF/AND statement.
Try this:
=IF(ISBLANK(Finish@row), "Gray", IF([%Complete]@row = 0, "Gray", IF([%Complete]@row = 1, "Green", IF(Finish@row > TODAY(+14), "Green", IF(Finish@row <= TODAY(+14), "Yellow", IF(AND([%Complete]@row < 1, Finish@row > TODAY()), "Red"))))))

Thank you Michael for your quick review. Unfortunately, when I add in that additional parentheses, it displays UNPARSEABLE. Do you have any other suggestions?

Hi @Lisa E.
If you're moving a parentheses you'll need to make sure you delete it from the end of the formula as well. One trick I use is to remove all the end parentheses and then hit Enter  Smartsheet will add in the correct number for you!
I've simplified your formula a little so there are less closing parentheses to deal with... let me know if this works for you and makes sense:
=IF(OR(ISBLANK(Finish@row), [%Complete]@row = 0), "Gray", IF(OR([%Complete]@row = 1, Finish@row > TODAY(14)), "Green", IF(Finish@row <= TODAY(14), "Yellow", IF(AND([%Complete]@row < 1, Finish@row > TODAY()), "Red"))))
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Thank you Genevieve! I see how you simplified the formula and it makes sense to me. Unfortunately, when I copied/pasted it, the field is displaying "UNPARESEABLE." Do you have any other suggestions?

Hi @Lisa E.
Would you be able to post a screen capture of your sheet with the column titles and the formula open in the cell? (But block out sensitive data).
The syntax is correct (correct commas and parentheses) so #UNPARSEABLE could mean that the column names are being referenced incorrectly.
For example, based on the initial question posted, I see you have
[%Complete]@row
However the default column name for a Project type of Smartsheet is
[% Complete]@row (with a space)
I would suggest that instead of copy/pasting, try writing the formula out and selecting the correct cell as you write. This will ensure the column name is populated exactly as it is in your sheet!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 200 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!