Getting "Unparseable" when adding a new factor to equation
I am trying to have my "status" column populate with "In Progress", "At Risk", "Late", "Complete" and "Not Started" based on the percent complete and the end date. I can get the first four to work but when I put in "Not Started" to equal the percent complete when it is empty, I get an error. Can anyone help?
=IF([% Complete]@row = 1, "Complete", IF(AND([% Complete]@row < 1, [End Date]@row <= TODAY()), "Late", IF(AND([% Complete]@row < 0.5, [End Date]@row <= TODAY(5)), "At Risk", (IF(AND([% Complete]@row < 1, [End Date]@row > TODAY(7)), "In Progress", IF([% Complete]@row = "", "Not Started"))))))))
Best Answers

You have a bracket before your IF after "At Risk" and your closing brackets are mismatched
=IF([% Complete]@row = 1, "Complete", IF(AND([% Complete]@row < 1, [End Date]@row <= TODAY()), "Late", IF(AND([% Complete]@row < 0.5, [End Date]@row <= TODAY(5)), "At Risk", IF(AND([% Complete]@row < 1, [End Date]@row > TODAY(7)), "In Progress", IF([% Complete]@row = "", "Not Started")))))
A bit of a warning..
You want to slice off your scenarios in nice bits to remove things that can slip through. There seem to be some holes in your logic..
Try a date 7 days out with 20% completion it will show nothing.. shouldn't it show "In Progress"
Also, 0% will show Nothing at 6 and 7 days but will show "In Progress at 8 days"
At what about 75% on the 6th day... shows Blank
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com

It is a combination of a few things..
The blank output is due to getting all the way through your if statement and not having something to match it to. A proper If is IF( logical_expression value_if_true [ value_if_false ]) where the [ value_if_false ] is optional. As a result, if it "falls through" if it shows blank
 If statements read from left to right
 One way could be to move your check for "" further left in the If statement.
 Your AND([% Complete]@row < 0.5, [End Date]@row <= TODAY(5) will pick up a 0% as under 50% and within 5 days so it will show "AT Risk" which is probably what you want
 Note that you have <= Today(5) and in your two If statements.
 This means that Items on Day 6 and Day 7 slide between these two dates
 Your "Not Started" Will only show if it is on Day 6 or day 7
 Once again your AND([% Complete]@row < 1, [End Date]@row > TODAY(7) will satisfy a 0% 8 days or greater as "In progress" as 0 is less then 100
 You might want to change the above to AND([% Complete]@row > .01, [End Date]@row > TODAY(7) as then it will not show Zero as not started
Also "" and 0 are not the same so if you have 0% in the completion it will not work
You might want to change the last statement IF(OR([% Complete]@row = "",[% Complete]@row = 0), "Not Started")
Try this.. it will get you closer
=IF([% Complete]@row = 1, "Complete", IF(AND([% Complete]@row < 1, [End Date]@row <= TODAY()), "Late", IF(AND([% Complete]@row < 0.5, [End Date]@row <= TODAY(5)), "At Risk", IF(AND([% Complete]@row >.01, [End Date]@row > TODAY(7)), "In Progress", IF(OR([% Complete]@row = "",[% Complete]@row = 0), "Not Started")))))
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
 If statements read from left to right
Answers

You have a bracket before your IF after "At Risk" and your closing brackets are mismatched
=IF([% Complete]@row = 1, "Complete", IF(AND([% Complete]@row < 1, [End Date]@row <= TODAY()), "Late", IF(AND([% Complete]@row < 0.5, [End Date]@row <= TODAY(5)), "At Risk", IF(AND([% Complete]@row < 1, [End Date]@row > TODAY(7)), "In Progress", IF([% Complete]@row = "", "Not Started")))))
A bit of a warning..
You want to slice off your scenarios in nice bits to remove things that can slip through. There seem to be some holes in your logic..
Try a date 7 days out with 20% completion it will show nothing.. shouldn't it show "In Progress"
Also, 0% will show Nothing at 6 and 7 days but will show "In Progress at 8 days"
At what about 75% on the 6th day... shows Blank
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com

Thank you! That fixed the issue. I agree, my logic is somewhat suspect :) I'm very new at all this.
Is it my logic that is keeping my "blank" in the % Complete field from populating "Not Started"? Its populating "Late" right now and I can't figure out what to change. Thank you, let me know if I should add this to a new question.
Lisa

Something like:
=IF([% Complete]@row = 1, "Complete", IF([End Date]@row <= TODAY(), "Late", IF(AND([% Complete]@row < 0.5, [End Date]@row <= TODAY(5)), "At Risk", IF(OR([% Complete]@row = "",[% Complete]@row = 0), "Not Started", "In Progress"))))
Might be a bit better.. although I don't know all your cases
 No matter what the date is if the % Complete is 100% then "Complete"
 Any Completion under 100% that is Today or in the past is marked "Late"
 We don't have to check if it is not complete as we have already ruled it out in Step 1
 Any Completion under 50% and End Date less than or equal to 5 days is marked at "Risk"
 If Complete is Zero OR Blank then "Not Started"
 ASSUME everything else is "In Progress"
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com

Hi  this helps a lot  shows me a different way to write these (a lot more concise!). Only problem is that when both fields are blank (% complete and end date) it shows "Late" when it should show "Not Started" as you described above.

It is a combination of a few things..
The blank output is due to getting all the way through your if statement and not having something to match it to. A proper If is IF( logical_expression value_if_true [ value_if_false ]) where the [ value_if_false ] is optional. As a result, if it "falls through" if it shows blank
 If statements read from left to right
 One way could be to move your check for "" further left in the If statement.
 Your AND([% Complete]@row < 0.5, [End Date]@row <= TODAY(5) will pick up a 0% as under 50% and within 5 days so it will show "AT Risk" which is probably what you want
 Note that you have <= Today(5) and in your two If statements.
 This means that Items on Day 6 and Day 7 slide between these two dates
 Your "Not Started" Will only show if it is on Day 6 or day 7
 Once again your AND([% Complete]@row < 1, [End Date]@row > TODAY(7) will satisfy a 0% 8 days or greater as "In progress" as 0 is less then 100
 You might want to change the above to AND([% Complete]@row > .01, [End Date]@row > TODAY(7) as then it will not show Zero as not started
Also "" and 0 are not the same so if you have 0% in the completion it will not work
You might want to change the last statement IF(OR([% Complete]@row = "",[% Complete]@row = 0), "Not Started")
Try this.. it will get you closer
=IF([% Complete]@row = 1, "Complete", IF(AND([% Complete]@row < 1, [End Date]@row <= TODAY()), "Late", IF(AND([% Complete]@row < 0.5, [End Date]@row <= TODAY(5)), "At Risk", IF(AND([% Complete]@row >.01, [End Date]@row > TODAY(7)), "In Progress", IF(OR([% Complete]@row = "",[% Complete]@row = 0), "Not Started")))))
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
 If statements read from left to right

Thank you so much. I think I got it to work, really appreciate the time you took to explain the logic to me. :)
Help Article Resources
Categories
Check out the Formula Handbook template!