IF AND RYG Formula

Lisa E.

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

Michael Culley

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

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