Red/Yellow/Green

lleslie001

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

Paul Newcome

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

@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

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.