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

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.

  • Awesome work! Thank you. – Iza Bella Nov 7 '19 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.