Red/Yellow/Green

Options

I'm attempting to use red/yellow/green logical function to highlight when a % is Meeting (green), Nearing (yellow), Missing (red) a goal. The Green and Red are easy...the yellow is tough!

This is what I'm trying...but not working:

=IF([Q1: 7.1.21 to 9.30.21 %]@row >= [Performance Target]@row, "Green", IF(AND([Q1: 7.1.21 to 9.30.21 %]@row <= [Performance Target]@row - 15, "Yellow"), IF(AND([Q1: 7.1.21 to 9.30.21 %]@row <= [Performance Target]@row - 30, "Red"))))


Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    If you only have 3 different outputs and two of them are easy, then just do those two and put the third in the final portion.


    =IF(this_is_true, "Green", IF(this_is_true, "Red", "Yellow"))


    It will output green if the green is true, red if the red is true, and then everything else just gets yellow.

  • lleslie001
    Options

    @Paul Newcome Humm...It's the middle ground of Yellow that Doesn't seem to work...example,

    Yellow if Performance Target is "nearing" Goal...in my brain, Yellow would be if the Value is within 10-30% of the goal. I attempted to use ([Performance Target]@row - 15) but no luck...

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Using your original formula, this is how I would go about modifying it for my suggested layout:


    =IF([Q1: 7.1.21 to 9.30.21 %]@row >= [Performance Target]@row, "Green", IF([Q1: 7.1.21 to 9.30.21 %]@row <= [Performance Target]@row - 30, "Red", "Yellow"))


    We do the green first which is greater than a specific number, then we do the red next which is less than a specific number. Everything else must be between those two numbers, so we just call it yellow.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!