Nested IF/AND/OR

jking108

I'm modifying a formula used for status tracking and I'm running into an unparseable error that I'm having problems resolving. I want to make the status red (stop sign) if the status is "in progress" or "not started" when the "at risk" column is checked. My understanding is that the formula will look for the first true statement and then stop looking.

Original formula:

=IF(Summary@row = 1, "", IF(Status@row = "Complete", "☑️", IF(Status@row = "Cancelled", "✖️", IF(AND(OR(Start@row = "", [Planned Finish]@row = ""), Status@row = "In Progress"), "🟢", IF(OR(Start@row = "", [Planned Finish]@row = ""), "🔘", IF(AND(OR(Status@row <> "Completed", Status@row <> "Completed", Status@row <> "Completed"), [Planned Finish]@row < Today@row), "🛑", IF(AND(Status@row = "Not Started", Start@row < Today@row), "⚠️", IF(Status@row = "In Progress", "🟢", "🔘"))))))))

Revised formula (addition after cancelled icon)

=IF(Summary@row = 1, "", IF(Status@row = "Complete", "☑️", IF(Status@row = "Cancelled", "✖️", IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress"), [At Risk]@row = 1), "🛑"), IF(AND(OR(Start@row = "", [Planned Finish]@row = ""), Status@row = "In Progress"), "🟢", IF(OR(Start@row = "", [Planned Finish]@row = ""), "🔘", IF(AND(OR(Status@row <> "Completed", Status@row <> "Completed", Status@row <> "Completed"), [Planned Finish]@row < Today@row), "🛑", IF(AND(Status@row = "Not Started", Start@row < Today@row), "⚠️", IF(Status@row = "In Progress", "🟢", "🔘")))))))))

Any ideas?

jking108

.... and I found a solution: There was an extra closing parenthesis after the first stop sign icon

=IF(Summary@row = 1, "", IF(Status@row = "Complete", "☑️", IF(Status@row = "Cancelled", "✖️", IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress"), [At Risk]@row = 1), "🛑", IF(AND(OR(Start@row = "", [Planned Finish]@row = ""), Status@row = "In Progress"), "🟢", IF(OR(Start@row = "", [Planned Finish]@row = ""), "🔘", IF(AND(OR(Status@row <> "Completed", Status@row <> "Completed", Status@row <> "Completed"), [Planned Finish]@row < Today@row), "🛑", IF(AND(Status@row = "Not Started", Start@row < Today@row), "⚠️", IF(Status@row = "In Progress", "🟢", "🔘")))))))))

Andrée Starå

Hi @jking108

I hope you're well and safe!

Excellent! Glad you got it working!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!