Harvey Ball Formula based on multiple criteria


I'm looking for a formula to populate the Harvey Ball color based on the Status of the project

For example:

In Progress = Green

In Progress and Delayed = Yellow

Not Started = Red

Complete = Blue

Cancelled = Blue

On Hold = Yellow

My formula is

=IF(Status@row = "In Progress", "Green", IF(Status@row = "In Progress and Delayed", "Yellow", IF(Status@row = "Complete", "Blue", IF(Status@row = "Not Started", "Red", IF(Status@row = "On Hold", "Yellow", IF(Status@row = "Cancelled", "Blue" )))))

but the colors won't change on Cancelled and In Progress & Delayed, and I believe I need to put a "AND" in the formula, but I don't know where.


Novice :)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!