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")))))))

Find more posts tagged with

Accepted answers

All comments

Michael Culley

It might be because you forgot a parentheses in the correct place for your IF/AND statement.

Try this:

Lisa E.

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

Genevieve P.

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

Lisa E.

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?

Genevieve P.

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.