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

Options
✭✭✭✭✭

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!