0

EDIT:

Okay. Thanks to BruceWayne, I have made progress.

Worksheet Screenshot

However, the formula I ended up with: =AND($C3="Y",OR($G3<=0.5,$H3<=0.5,$I3<=0.5,$J3<=0.5,$K3<=0.5,$L3<=0.5,$M3<=0.5,$N3<=0.5,$O3<=0.5,$P3<=0.5))

turns all the conditionally formatted cells of the row green simply because there is a Y in the cell of column C.

The formula should ideally check if a cell in column C has a Y in it, and a cell on the same row within columns H:P has a value <=0.5. If both values are TRUE, then only the cell with a value <=0.5 should be formatted GREEN.

In my included picture, cell H7 is colored GREEN even though it is 0.99 (>0.5). This appears to be because the formula is only checking if a Y is in column C row 7.


I don't understand Excel conditional formatting jargon so please explain it in layman's terms.

If a cell (eg: C1) in column C contains the value: Y, and ANY cell of the same row in columns H:K is =<0.5 (e.g. K1), then make those cells (e.g. K1) turn GREEN. The (G) in the diagram denotes a cell that should be green.

(Diagram for clarity)

| Column C | Column H | (Column MID) | Column K |
|          |          |              |          |
|    Y     |   0.6    |     0.9      |  0.3(G)  |
|          |          |              |          |
| (empty)  |   0.5    |     0.4      |  0.3     |
|          |          |              |          |
|    Y     |   0.5(G) |     0.2(G)   |  0.9     |


I tried =AND($C$3="Y",$G$3:$P$75="<0.5")

I tried =IF(AND($C$3:$C$75="Y",$G$3:$P$75="<0.5"),"")

I plugged both formulas into the conditional format manager and set the output format to GREEN, but I'm missing something because no cells turned green.

  • Thank you robinCTS for the clean edits. Others had trouble with my question as it was, but it reads much more clearer now. – Ebonezra Jun 14 '18 at 16:28
3

You actually only need three conditional formatting rules - your third one is redundant.

To work correctly as required, the formula for your last rule needs to be changed to:

=AND($C3="Y",G3<=0.5,G3<>"")

Worksheet ScreenShot


To be able to write/modify conditional formatting formulas, you need to know a couple of things.


Firstly you need to understand the difference between relative and absolute addressing.

Basically, absolute addressing is when there is a $ in front of the letters and/or the numbers of an address, e.g. the column C in $C3 is an absolute column reference. As you move from cell to cell to the right, the example address, $C3, remains the same as the column is absolute. (As you move down, though, the address will change, as the row is relative.)

Relative addressing is when there is no $, e.g. the row 3 in $C3 and the column G in G3. As you move to the right, the column part of that address changes. For G3, in the next cell to the right it changes to H3. For the next cell along it is I3, and so on.


The second thing you need to know is that when entering the formula for a conditional formatting rule, it is treated as if it were entered in the top-left cell of the "Applies to" range, and when Excel applies it to the cells in that range to work out the cells' formats, the relative references change as described above.

So, what your latest formula

=AND($C3="Y",OR($G3<=0.5,$H3<=0.5,$I3<=0.5,$J3<=0.5,$K3<=0.5,$L3<=0.5,$M3<=0.5,$N3<=0.5,$O3<=0.5,$P3<=0.5))

is actually doing (for the first row of the "Applies to" range) is, "for every cell in the row, if $C3="Y" and if any of the cells from $H3 to $P3 is <= 0.5, then set the cell's colour to green". The same thing happens, appropriately, for every other row in the "Applies to" range.

What you need to be doing instead is "for every cell in the row, if $C3="Y" and if the current cell is <= 0.5, then set the cell's colour to green".

Since, as I have explained above, when the formula is entered in the Conditional Formatting Rules Manager it is treated as if entered in the top-left cell of $G$3:$P$75(cell $G$3), "the current cell" is the relative reference G3.

Notes:

For your formula to actually work correctly, a check to see if the cell is not blank is also required. A blank cell is treated as a 0 when comparing with a number, and since 0 <= 0.5, all the blank cells would be coloured green.

  • 2
    Wow! Thank you so much for that clarification and respectful answer, robinCTS! I appreciate you taking the time to spell it out for me. I'm also grateful you recognized a major flaw in that 0<=0.5, because I had completely missed this point. – Ebonezra Jun 14 '18 at 16:24
0

You can do:

=AND($C2="Y",OR($H2<=.5,$I2<=.5,$J2<=.5,$K2<=.5))

enter image description here

  • 1
    Thank you Batm- BruceWayne, this worked, except under the "Applies to:" box, I used =$G$3:$P$75. These are the cells I want to turn green, and they did. However, the entire row turned green starting from G3 to P3 if C3 was TRUE. I only want the cells that contain the value <=0.5 to be green as there are other values on those rows that might be >0.5 and therefore should not be green. – Ebonezra Jun 12 '18 at 21:59
  • I've posted a picture clarifying my problem with BruceWayne's formula. It worked, but not as expected. I have green rows where individual cells should be green. I adjusted the formula according to my specific needs as per other suggestions, but even with the original formula idea, green rows were produced. Also, in the picture, H7 is Green, but it shouldn't be because it is >0.5 – Ebonezra Jun 12 '18 at 22:23

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.