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

• ✭✭✭✭✭

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

• ✭✭✭✭✭

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

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"

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

• 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!