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
-
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
-
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"
-
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
-
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
-
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!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 360 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!