Nested IF Formula - Project Health

Hello! I'm struggling to complete this formula without receiving errors. I am currently attempting to create formula in a project tracking sheet to return colored symbols (R,G,Y,G) into a "Project Health" Column. Below is the formula that I have so far. It is working up to this point, but when I try to add on IF statements (I've been attempting to utilize the best practice of building logical statements, one by one) to return the "red" dot, I receive error messages.


=IF(Status@row = "Complete", "Green", IF(Status@row = "Holding", "Gray", IF(Status@row = "Not Needed", "Gray", IF([Approval Status]@row = "Declined", "Yellow", IF(Deadline@row = TODAY(-7), Status@row <> "Complete", "Yellow")))))


The following is a set of all criteria I'm hoping to meet:

  • "Project Health" turns "Green" for any given row that has a status of "Complete" or if the Deadline is "3 weeks out or further"
  • "Project Health" turns "Yellow" for any given row that does not have a status of "Complete", "Holding", or "Not Needed", and the deadline is within the next 7 Days
  • "Project Health" turns "Gray" for any given row that has a "status" of "Holding" or "Not Needed"
  • "Project Health" turns "Red" for any given row that does not have a "Status" of "Complete", "Holding", or "Not Needed", and the deadline is within the next 3 days or is in the past.

Thanks in advance for the help!

Best Answers

Answers

  • Thank you both so much, this is extremely helpful!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!