Can someone help me with nested expressions?

mkinsey

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

Colleen Patterson

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

Colleen Patterson

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

Colleen Patterson

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

Colleen Patterson

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

mkinsey

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

Colleen Patterson

@mkinsey happy to help!