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
