# Need help with Health formula

Options
✭✭

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.

• ✭✭✭✭
Options

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")))

Cheers,

John

• ✭✭✭✭
Options

How are you assessing risk to determine if the cell should be green or yellow? Is it based off of a number of days left until due date or some other criteria? A nested if formula should work in this case, but need to know you calculate the risk level.

Cheers,

John

• ✭✭
Options

Yes, apologies for not being clear ... YELLOW will be based on 1 day left until due date.

• ✭✭✭✭
Options

The formula below should handle what you are looking to do and can be converted to a column formula. It should show red for overdue, yellow for due today or tomorrow, and green for anytime in the future.

=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"))

Cheers,

John

• ✭✭
Options

This is working great!! I need just the actual tasks to have a RED/YELLOW/GREEN dot and not the parent - so if it has a 1 in the Level column. All columns have a dot now.

• ✭✭
Options
• ✭✭✭✭
Options

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")))

Cheers,

John

• ✭✭
Options

This did the trick!! Thank you so much!!

• ✭✭✭✭
Options

You're welcome.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!