Using "Flag" in and IF function

Options

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?

Tags:

Best Answer

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭
    Answer ✓
    Options

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


Answers

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭
    Answer ✓
    Options

    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.
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!