Unparseable Formula

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

Answers

  • Michael Mulford
    edited 01/24/22

    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

  • 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
    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"))

  • 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
    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].

  • 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
    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"))

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

  • Paul Newcome
    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"))

  • 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
    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")))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!