Can someone help me with nested expressions?

Options

Hello ! 

I'm trying to create a formula that will assign the appropriate symbol for each row in a project sheet to represent the task's health. I eventually want to query this column in a dashboard widget. 

There are five expressions. I've tested each individual expression successfully. However when I start to combine them, I get stuck at the third expression, resulting in an #UNPARSABLE error. See below for the specifics. 

SEPERATE EXPRESSIONS ALL SUCCESSFUL

1. If task is 100% complete, the symbol should be BLUE

=IF([% Done]@row = 1, "Blue")

2. If task is 0% AND Start is in the past and Due Date is in the past, the Symbol should be RED

=IF(AND([% Done]@row = 0, Start@row < TODAY(), [Due Date]@row < TODAY()), "Red")

3. If task is 0% AND Start is in the past and Due Date is today or in the future, the Symbol should be YELLOW

=IF(AND([% Done]@row = 0, Start@row < TODAY(), [Due Date]@row >= TODAY()), "Yellow")

4. If task is > 0% AND Due Date is in the past, the Symbol should be YELLOW

=IF(AND([% Done]@row > 0, [Due Date]@row < TODAY()), "Yellow")

5. If task is > 0% AND Due Date is today or in the future, the Symbol should be GREEN

=IF(AND([% Done]@row > 0, [Due Date]@row >= TODAY()), "Green")


COMBINED EXPRESSIONS  

1 & 2 - success

=IF([% Done]@row = 1, "Blue", IF(AND([% Done]@row = 0, Start@row < TODAY(), [Due Date]@row < TODAY()), "Red"))

1 & 2 & 3 - fails with #UNPARSABLE

=IF([% Done]@row = 1, "Blue", IF(AND([% Done]@row = 0, Start@row < TODAY(), [Due Date]@row < TODAY()), "Red"), IF(AND([% Done]@row = 0, Start@row < TODAY(), [Due Date]@row >= TODAY()), "Yellow")))

Cannot proceed with adding 4 & 5 until adding 3 is successful.

Thank you in advance for your assistance :) 

Mike

Best Answers

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    edited 02/10/23 Answer ✓
    Options

    @mkinsey

    =IF([% Done]@row = 1, "Blue", IF(AND([% Done]@row = 0, Start@row < TODAY(), [Due Date]@row < TODAY()), "Red", IF(AND([% Done]@row = 0, Start@row < TODAY(), [Due Date]@row >= TODAY()), "Yellow", " ")))

    This takes you to the third level. But I also included all nested, which I tested in the screen shot below.

    =IF([% Done]@row = 1, "Blue", IF(AND([% Done]@row = 0, Start@row < TODAY(), [Due Date]@row < TODAY()), "Red", IF(AND([% Done]@row = 0, Start@row < TODAY(), [Due Date]@row >= TODAY()), "Yellow", IF(AND([% Done]@row > 0, [Due Date]@row < TODAY()), "Yellow", IF(AND([% Done]@row > 0, [Due Date]@row >= TODAY()), "Green", " ")))))


    Smartsheet Community Champion and Ambassador

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    Answer ✓
    Options

    In your example, you were closing the equation after "Red", which was flagging it as #UNPARSABLE

    Smartsheet Community Champion and Ambassador

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

Answers

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    edited 02/10/23 Answer ✓
    Options

    @mkinsey

    =IF([% Done]@row = 1, "Blue", IF(AND([% Done]@row = 0, Start@row < TODAY(), [Due Date]@row < TODAY()), "Red", IF(AND([% Done]@row = 0, Start@row < TODAY(), [Due Date]@row >= TODAY()), "Yellow", " ")))

    This takes you to the third level. But I also included all nested, which I tested in the screen shot below.

    =IF([% Done]@row = 1, "Blue", IF(AND([% Done]@row = 0, Start@row < TODAY(), [Due Date]@row < TODAY()), "Red", IF(AND([% Done]@row = 0, Start@row < TODAY(), [Due Date]@row >= TODAY()), "Yellow", IF(AND([% Done]@row > 0, [Due Date]@row < TODAY()), "Yellow", IF(AND([% Done]@row > 0, [Due Date]@row >= TODAY()), "Green", " ")))))


    Smartsheet Community Champion and Ambassador

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    Answer ✓
    Options

    In your example, you were closing the equation after "Red", which was flagging it as #UNPARSABLE

    Smartsheet Community Champion and Ambassador

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

  • mkinsey
    mkinsey ✭✭
    Options

    Thank you @Colleen Patterson for your simple and concise explanation 😀

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    Options

    @mkinsey happy to help!

    Smartsheet Community Champion and Ambassador

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!