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

  • Michael Culley
    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.
    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.
    Genevieve P. Employee Admin

    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.
    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.
    Genevieve P. Employee Admin

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!