Using "Flag" in and IF function

Raluca S.

Hello!

I am looking to create a formula for the RYG column that would work like this:

If Status column is "Overdue", then it shows "Yellow"

If Status column is "Complete", then it shows "Green"

If Status column is "In Progress", then it shows "Green"

If Status column is "Not Started", then it shows "Grey"

If At Risk column is flagged, then it shows "Red"

I have been trying this, but the At Risk flag doesn't trigger Red.

=IF(Status@row = "Overdue", "Yellow", IF(Status@row = "Complete", "Green", IF(Status@row = "In Progress", "Green", IF(Status@row = "Not Started", "Gray", IF([At Risk]@row = 1, "Red")))))

Any ideas?

Danielle Arteaga

I think the issue is the order of your conditions. The formula is parsed in the order you write it, so as soon as a condition is "true," Smartsheet stops evaluating conditions. That means, for example, that as soon as Smartsheet sees a status is "Overdue," it puts the yellow ball in your symbol column and stops evaluating the rest of the conditions, including the "At Risk" flag.

Try this (using the right titles for your columns):

=IF([At Risk]@row = 1, "Red", IF(Status@row = "Overdue", "Yellow", IF(OR(Status@row = "Complete", Status@row = "In Progress"), "Green", IF(Status@row = "Not Started", "Gray", "Gray"))))

Danielle Arteaga

I think the issue is the order of your conditions. The formula is parsed in the order you write it, so as soon as a condition is "true," Smartsheet stops evaluating conditions. That means, for example, that as soon as Smartsheet sees a status is "Overdue," it puts the yellow ball in your symbol column and stops evaluating the rest of the conditions, including the "At Risk" flag.

Try this (using the right titles for your columns):

=IF([At Risk]@row = 1, "Red", IF(Status@row = "Overdue", "Yellow", IF(OR(Status@row = "Complete", Status@row = "In Progress"), "Green", IF(Status@row = "Not Started", "Gray", "Gray"))))

Raluca S.

Amazing! Thank you, it worked!! I will remember this.