Conditional formatting against Min or Max values in a row

[stugale]

Hi

I'm afraid I'm having a complete mental blockage.

I need to CF cells based on whether a cell is within a range of 2 either way of the maximum or minimum for that row.

[TABLE="width: 216"]
<colgroup><col><col span="3"><col><col></colgroup><tbody>[TR]
[TD]PC1[/TD]
[TD]PC2[/TD]
[TD]PC3[/TD]
[TD]PC4[/TD]
[TD]PC5[/TD]
[TD]PC6[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]8.5[/TD]
[TD]9[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]8.5[/TD]
[TD]9[/TD]
[TD]7.5[/TD]
[TD]9.5[/TD]
[TD]9[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]8[/TD]
[TD]7[/TD]
[TD]7.5[/TD]
[TD]9[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]7.5[/TD]
[TD]8[/TD]
[TD]7[/TD]
[TD]7.5[/TD]
[TD]8[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]7.5[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]7.5[/TD]
[TD]6.5[/TD]
[TD]3[/TD]
[TD]9.5[/TD]
[TD]7[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]9[/TD]
[TD]7[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]

For example in the 1st row under the headings I need the PC2 score of 6 highlighted and the PC1 & PC5 scores of 9 highlighted as 6 is more than 2 less than the max and 9 is more than 2 greater than the minimum.

I'm sure this is a real basic query but as mentioned I'm having a complete block so any help would be greatly appreciated.

Thanks everyone

 

[crazydragon84]

you need to use formula to apply the conditional format. Go to Conditional Format, New Rule, Use a formula to Determine which Cells to Format. Type in formula like this for A1.


i don't know why but the below formula keeps getting cut off when i post...

=AND(A1=MIN(A:A),A1 LESS THAN MAX(A:A)-2)

it seems this form thinks less than sign is part of html code and cuts it off.

Maybe this will work...
=AND(A1=MIN(A:A),MAX(A:A)-2 > A1)<max(a:a)-2)[ quote]<max(a:a)-2)<max(a:a)-2)"<="" html=""></max(a:a)-2)[>

 

[stugale]

Thanks for the response. Unfortunately it didn't work.

I think my question was worded incorrectly originally.

What I need to do is CF cells in each row based on the range of numbers. For example in the row below I need it to CF the cell containing 6 and cells containing 9 as they are more than 2 difference. I need this to be for every row but only against cells within that row.

[TABLE="class: cms_table, width: 216"]
<tbody>[TR]
[TD]PC1[/TD]
[TD]PC2[/TD]
[TD]PC3[/TD]
[TD]PC4[/TD]
[TD]PC5[/TD]
[TD]PC6[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]8.5[/TD]
[TD]9[/TD]
[TD]8
[/TD]
[/TR]
</tbody>[/TABLE]

Hopefully that makes more sense

Many Thanks

 

[crazydragon84]

Sorry mine was doing it vertically instead of horizontally.

=AND(A1=MIN(1:1),MAX(1:1)-2 > A1)

this will highlight the 6 in your case. then, have another CF

=AND(A1=MAX(1:1),A1 > Min(1:1) +2)

This will highlight the 9.

 

[Marcelo Branco]

Maybe...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

PC1​

[/TD]
[TD]

PC2​

[/TD]
[TD]

PC3​

[/TD]
[TD]

PC4​

[/TD]
[TD]

PC5​

[/TD]
[TD]

PC6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD="bgcolor: #FFFF00"]

9​

[/TD]
[TD="bgcolor: #FFFF00"]

6​

[/TD]
[TD]

8​

[/TD]
[TD]

8,5​

[/TD]
[TD="bgcolor: #FFFF00"]

9​

[/TD]
[TD]

8​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

8,5​

[/TD]
[TD]

9​

[/TD]
[TD]

7,5​

[/TD]
[TD]

9,5​

[/TD]
[TD]

9​

[/TD]
[TD]

8​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

8​

[/TD]
[TD]

8​

[/TD]
[TD]

7​

[/TD]
[TD]

7,5​

[/TD]
[TD]

9​

[/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

7,5​

[/TD]
[TD]

8​

[/TD]
[TD]

7​

[/TD]
[TD]

7,5​

[/TD]
[TD]

8​

[/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD="bgcolor: #FFFF00"]

8​

[/TD]
[TD="bgcolor: #FFFF00"]

4​

[/TD]
[TD]

6​

[/TD]
[TD="bgcolor: #FFFF00"]

8​

[/TD]
[TD]

7,5​

[/TD]
[TD="bgcolor: #FFFF00"]

8​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

7,5​

[/TD]
[TD]

6,5​

[/TD]
[TD="bgcolor: #FFFF00"]

3​

[/TD]
[TD="bgcolor: #FFFF00"]

9,5​

[/TD]
[TD]

7​

[/TD]
[TD]

8​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

7​

[/TD]
[TD]

7​

[/TD]
[TD]

7​

[/TD]
[TD]

7​

[/TD]
[TD]

7​

[/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

7​

[/TD]
[TD="bgcolor: #FFFF00"]

6​

[/TD]
[TD="bgcolor: #FFFF00"]

6​

[/TD]
[TD="bgcolor: #FFFF00"]

9​

[/TD]
[TD]

7​

[/TD]
[TD]

7​

[/TD]
[/TR]
</tbody>[/TABLE]


Select A2:F9

In CF use this formula
=AND(OR(A2=MIN($A2:$F2),A2=MAX($A2:$F2)),MAX($A2:$F2)-MIN($A2:$F2)>2)
pick the format you want (fill --> yellow in the example)

Hope this helps

M.