Need help with Health Status Formula

ccousineau

I have a sheet with 3 columns: Action, Status, Health

I want the health to populate a green, red, yellow, or gray circle depending on which status the action is in. The status and corresponding health colors are:

Blank = Red

Not Started = Red

In progress = Yellow

Completed = Green

Cancelled = Gray

On Hold = Gray

Looking for a formula that allows all of the above to be true!

Thanks so so much!

Nic Larsen

=IF([Status]@row = "Completed", "Green", IF([Status]@row = "In progress", "Yellow", IF(OR([Status]@row = "Cancelled", [Status]@row = "On Hold"), "Gray", "Red")))