Conditional formatting question

[Lade]

Hello,

Hopefully this will all make sense.

I have a spreadsheet that I enter measurements into. I have all the parts in rows 17 - 117 and the measurements in columns W - AN. If the measurement is below 0.7 then I want that cell to turn red, which is no problem. But where I'm running into issues is the next step.
I want the header of each column to turn red when there is a measurement that fails in that column. For example, if the measurement in cell W20 is 0.4, then I want the header for column W to turn red. For some reason I can't get anything to work right.

Currently this is what I have in the conditional formatting as the formula for the header (cells W16 - AN16):

=AND(OR((W$17:W$117)>0),(OR((W$17:W$117)<0.7)))

The reason I'm not just using a simple <0.7 is because excel then flags all the blank cells in that range also that haven't been filled in yet. So I'm trying to do ">0 and <0.7", that way it doesn't flag blanks. This is the latest formula I've ended up with so far through research and fiddling around with it. The problem with it is that it does absolutely nothing LOL, nothing changes in the header when I type a value into the table greater than 0 but less than 0.7.

Anything thoughts?

 

[Anonymous1378]

Perhaps consider using:

=OR((W$17:W$117>0)*(W$17:W$117<0.7))

 

[Joe4]

Should be able to use a simple COUNTIFS formula:

=COUNTIFS(W$17:W$117,">0",W$17:W$117,"<0.7")>0

 

[Lade]

Perhaps consider using:

=OR((W$17:W$117>0)*(W$17:W$117<0.7))

Thanks! That worked! Is that a "times" symbol in there between the two? How does that work?

 

[Anonymous1378]

The two conditions each produce an array of zeroes and ones, which are then multiplied with each other. A row which meets both conditions will be one where a one is multiplied with another one. One is recognized as TRUE by excel, and the OR function ensures that if any ones appear in the array, the conditional formatting rule is triggered.

You can see what the array looks like by pasting the formula into a regular cell.

 

[Joe4]

The two conditions each produce an array of zeroes and ones, which are then multiplied with each other. A row which meets both conditions will be one where a one is multiplied with another one. One is recognized as TRUE by excel, and the OR function ensures that if any ones appear in the array, the conditional formatting rule is triggered.

You can see what the array looks like by pasting the formula into a regular cell.

Interesting, but I think it may be a little unnecessarily complicated, when there is already a built-in function that does that, at least in this case.

 

[Lade]

Should be able to use a simple COUNTIFS formula:

=COUNTIFS(W$17:W$117,">0",W$17:W$117,"<0.7")>0

Just for curiosity I tried this one also and it worked too! I guess there's more than one way to skin a cat. Thanks for both of your help, I really appreciate it!

 

[Joe4]

Just for curiosity I tried this one also and it worked too! I guess there's more than one way to skin a cat. Thanks for both of your help, I really appreciate it!

Yes, that was my point - is that there is already a function that was designed to do exactly what you are asking, so there is no need to re-invent the wheel.
See here for more details on the COUNTIFS function: MS Excel: How to use the COUNTIFS Function (WS)