0

My question is in relation to excel conditional formatting. Can you use a formula in conditional formatting to highlight cells in column M based on the inequality signs and the values entered in columns G - L?

PFA image

     G         H          I           J         K         L    |   M
  Green       Green      Amber       Amber      Red      Red   |Oct - 19  
    <=        100         <=         99.9        <         99  |
    <=        100         <=         88.4        <         50  |
     =          0         >            0         >         1   |

As you can see, columns G, J and K have inequality signs (<, >, <=, >=, <> or =). Columns H, J and L include values or numbers. Can you use conditional formatting on column M "Oct - 19" based on the green, amber and red thresholds specified in columns G - L?

I know you can use the below formula (to highlight the cell green) however this will not apply as the inequality signs change for each test (as you can see, I have over 200 rows and I would like not to include 627 different rules in this file).

=M2<=$H2

1 Answer 1

0

Select M2:M5 (or more, or even whole column).

Open Conditional formatting - Using formula.

Enter the formula =COUNTIFS(M2;K2&L2)=1 and specify some format.

Then enter one more rule using formula =COUNTIFS(M2;I2&J2)=1 with another format, and 3rd rule =COUNTIFS(M2;G2&H2)=1 with 3rd format.

enter image description here

PS. Last 2 symbols =1 in formulas are, of course, excess.

1
  • Awesome work! Thank you.
    – Iza Bella
    Nov 7, 2019 at 13:51

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .