Getting "Unparseable" when adding a new factor to equation

lisamurzyn

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

Brent Wilson

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 Wilson

@lisamurzyn

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 Wilson

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

lisamurzyn

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

Brent Wilson

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

1. No matter what the date is if the % Complete is 100% then "Complete"
2. Any Completion under 100% that is Today or in the past is marked "Late"
   1. We don't have to check if it is not complete as we have already ruled it out in Step 1
3. Any Completion under 50% and End Date less than or equal to 5 days is marked at "Risk"
4. If Complete is Zero OR Blank then "Not Started"
5. ASSUME everything else is "In Progress"

lisamurzyn

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.

Brent Wilson

@lisamurzyn

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

lisamurzyn

Thank you so much. I think I got it to work, really appreciate the time you took to explain the logic to me. :-)