Need help with Health formula


Created a project plan with hierarchies.

Created a column called LEVEL which gets a 1 if line item is a task ..this is working fine and I'm able to create reports.

Created a column called HEALTH which gets a red/yellow/green ball depending on whether the task is overdue (red), on target (green) or some risk (yellow).

The formula for RED works fine by itself:

=IF(AND([End Date]@row < TODAY(), Status@row <> "Complete", Level@row = "1"), "Red")

The formula for GREEN works fine by itself:

=IF(AND([Status]@row = "Complete", Level@row = "1"), "Green")

I can't get both to work at the same time, and need help with creating formula for YELLOW.

Thank you.

Best Answer

  • John Shane
    John Shane ✭✭✭✭
    Answer ✓


    This should remove the status symbols from any row where Level does not equal 1 without affecting the rest of the rows.

    =IF(Level@row <> 1, "", IF(AND([End Date]@row < TODAY(), Status@row <> "Complete", Level@row = "1"), "Red", IF(AND([End Date]@row < TODAY(2), Status@row <> "Complete", Level@row = "1"), "Yellow", "Green")))




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!