Multiple IFS Formula for Status based on % Complete + Finish Date

Lisa Matthews

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

markkrebs

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

Lisa Matthews

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

Mike TV

@Lisa Matthews

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.

BullandKhmer

https://community.smartsheet.com/discussion/comment/374701#Comment_374701

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.