Multiple IFS Formula for Status based on % Complete + Finish Date
Hello,
I broke a working formula by changing the conditions and hoping to get some help to get this to work.
Objective: Create a formula to reflect the status of task based on [% Complete] and [Finish]. These are the values I am trying to generate in one formula:
 Red (Late) = If % complete is less than 100% and Finish Date is in the past
 Yellow (At Risk) = % complete is less than 85% and Finish Date is within the next 3 days. (% complete greater than 85% + Finish Date within next 3 days is Green "In Progress")
 Green "In Progress" = % complete is greater than 0% and Finish Date is greater than today + 4 days
 Blue "Not Started" = % complete is 0% and Finish Date is greater than today + 4 days
 Gray "Complete" = % complete is 100%
 Purple "On Hold" = % complete is “H”
 Black "Cancelled" = % complete is “X”
This is the formula I have so far (it doesn't work) and it is not complete. Not sure why the formula is not looking at the % complete for "At Risk" in the image below:
Thank you, in advance, for your help with this formula.
Lisa
Answers

You are looking at a % so instead of 85 put a .85

Hello @markkrebs , I did try .85 and the result was a blank cell for rows which should have been "At Risk" or "Complete". Not sure how to fix this formula, still. Thank you for trying to help.
Lisa

Your first problem is your formula starts with =IF([% Complete]@row=85, but then it doesn't tell SmartSheet what to do if that equals 85. You just go right into the next Nested IF without resolving the first IF statement's True statement.

Yeah this.... so the rest of the formula runs only IF % complete=85... so you almost always get a blank cell.... its best to start with the most definitive statements in a nestled IF formula.
Another tip  nestled IF statements work from the first applicable statement, so you want to start from the most definitive statement... in your case I would order your IF statement Gray, Purple, Black, Red, Blue, Green, Yellow.
Finally, I'd consider separating 'Status' i.e. on hold, cancelled etc. from % complete. so your % complete figure isn't "H" or "X"... this will cause issues building metrics and generally referencing the % complete column.
I'm too lazy to figure out the details. cheers.
Help Article Resources
Categories
Check out the Formula Handbook template!