Unparseable Formula

Samantha333

Can anyone see something obvious that is causing this formula to throw an "unparseable" error?

=IF(AND(Status4 = "Not Started", [Workdays since Start Date]4 < 0), "Green", IF(AND(Status4 = "Not Started", [Workdays since Start Date]4 > 0), "Red", IF((AND)Status4 = "In Progress", [Workdays since Start Date]4 < 0), "Green", IF(AND(Status4 = "In Progress", [Workdays since Start Date]4 > 0, [% Complete]4 < [% of Duration Passed]4), "Yellow", IF(AND(Status4 = "In Progress", [Workdays since Start Date]4 > 0, [% Complete]4 >= [% of Duration Passed]4), "Green", "Red"))))

Michael Mulford

Hi Samantha,

You have some misplaced parenthesis....

=IF(AND(Status4 = "Not Started", [Workdays since Start Date]4 < 0), "Green", IF(AND(Status4 = "Not Started", [Workdays since Start Date]4 > 0), "Red", IF((AND)Status4 = "In Progress", [Workdays since Start Date]4 < 0), "Green", IF(AND(Status4 = "In Progress", [Workdays since Start Date]4 > 0, [% Complete]4 < [% of Duration Passed]4), "Yellow", IF(AND(Status4 = "In Progress", [Workdays since Start Date]4 > 0, [% Complete]4 >= [% of Duration Passed]4), "Green", "Red"))))

....and I suggest you use the @row instead of the row number.

Try this:

=IF(AND(Status@row = "Not Started", [Workdays since Start Date]@row < 0), "Green", IF(AND(Status@row = "Not Started", [Workdays since Start Date]@row > 0), "Red", IF(AND(Status@row = "In Progress", [Workdays since Start Date]@row < 0), "Green", IF(AND(Status@row = "In Progress", [Workdays since Start Date]@row > 0, [% Complete]@row < [% of Duration Passed]@row), "Yellow", IF(AND(Status@row = "In Progress", [Workdays since Start Date]@row > 0, [% Complete]@row >= [% of Duration Passed]@row), "Green", "Red")))))

After that, make it a column formula, so it applies to the new rows.

All the best,

Mike

Samantha333

Thank you, Mike! This is working for all but one of the use cases. I've indicated "YES" where the correct result is populating and NO where it is not working.

YES - Not Started, Before Start Date = GREEN (or gray)

YES - Not Started, After Start Date = RED

YES -  In Progress, Before Start Date = GREEN

YES - In Progress, After Start Date, Before End Date

YES - If % complete < % of timeframe passed, YELLOW

YES If % complete >= % of timeframe passed, GREEN

NO -  In Progress, After End Date = RED

Additionally, I would like it to be "Blue" if the status is "Complete" but haven't been able to work that in yet due to my struggles with the last item above. Here is a screenshot of the columns referenced in the formula. The Status column Autopopulates based on the % complete.

Formula in Status column:

=IF([% Complete]@row = 1, "Complete", IF([% Complete]@row > 0.01, "In Progress", "Not Started"))

Formula in Duration Calculation:

=NETWORKDAY([Start Date]106, [Target End Date]106)

Formula in % of Duration Passed:

=[Workdays since Start Date]106 / [Duration Calculated]106

Paul Newcome

Really you should be able to just use the % complete comparison and not have to worry about including anything regarding the status.

=IF(AND([% Complete]@row<1, [End Date]@row< TODAY()), "Red", IF([% Complete]@row>= NETWORKDAYS([Start Date]@row, TODAY()) / [Duration Calculation]@row, "Green", "Yellow"))

Samantha333

Thank you for the feedback, Paul. The logic makes sense to me. However, when I use that formula, I am receiving a blank outcome. If I hover over it, it says "#UNPARSEABLE"

Paul Newcome

Please double check that I used the correct column names. My fingers and brain haven't been exactly the most synced up today. Haha. It may be that I used [End Date] instead of [Target End Date].

Samantha333

Thank you! I did find a couple of columns names that needed to be corrected. Here is the resulting formula:

=IF(AND([% Complete]@row < 1, [Target End Date]@row < TODAY()), "Red", IF([% Complete]@row >= NETWORKDAYS([Start Date]@row, TODAY()) / [Duration Calculated]@row, "Green", "Yellow"))

This is working for some but not all of the scenarios. See below. YES means it's working, NO means it is not. Any ideas on adjusting the formula to account for the NO's?

YES - Not Started, Before Start Date = GREEN

NO - Not Started, After Start Date = RED (This scenario is turning yellow instead, with 0% entered as % complete)

YES - In Progress, Before Start Date = GREEN

YES - In Progress, After Start Date, Before End Date

_____YES - If % complete < % of timeframe passed, YELLOW

_____YES If % complete >= % of timeframe passed, GREEN

YES -  In Progress, After End Date = RED

Paul Newcome

Ok. Quick adjustment...

=IF(OR(AND([% Complete]@row < 1, [Target End Date]@row < TODAY()), AND([% Complete]@row = 0, [Start Date]@row< TODAY()), "Red", IF([% Complete]@row >= NETWORKDAYS([Start Date]@row, TODAY()) / [Duration Calculated]@row, "Green", "Yellow"))

Samantha333

Thank you for the feedback. This is giving a blank result and the error #INCORRECT ARUGMENT SET

Paul Newcome

Because I missed one of those troublesome parenthesis. there should be 3 closing parenthesis after the second TODAY. Make sure you remove the one that was automatically put at the end if you manually add in the missing one. If you copy/paste the below we should be working now.

=IF(OR(AND([% Complete]@row < 1, [Target End Date]@row < TODAY()), AND([% Complete]@row = 0, [Start Date]@row< TODAY())), "Red", IF([% Complete]@row >= NETWORKDAYS([Start Date]@row, TODAY()) / [Duration Calculated]@row, "Green", "Yellow"))

Samantha333

Beautiful! It works!

Is there any way to adjust the formula such that it automatically turns blue when status is complete?

Thank You!

Paul Newcome

Try this...

=IF([% Complete]@row < 1 = 1, "Blue", IF(OR([Target End Date]@row < TODAY(), AND([% Complete]@row = 0, [Start Date]@row< TODAY())), "Red", IF([% Complete]@row >= NETWORKDAYS([Start Date]@row, TODAY()) / [Duration Calculated]@row, "Green", "Yellow")))