Conditional Formatting to find Top 3 Values in Multiple Columns

[Anonymity21]

Good afternoon everyone! I have a spreadsheet that I'm trying to conditionally format to display the top 3 values in each column (not including the value of 1). I believe I have to use an AND(LARGE()) type function, but I can't seem to get it to work. Any ideas?

 

[Caribeiro77]

If you don't have dups this might work:

Unknown
[Table="width:, class:head"][tr=bgcolor:#888888][th]_Row\^Col[/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]

3​

[/td][td]

4​

[/td][td]

5​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td=bgcolor:#FFFF00]

14​

[/td][td=bgcolor:#FFFF00]

6​

[/td][td=bgcolor:#FFFF00]

25​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]

4​

[/td][td]

5​

[/td][td]

6​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td=bgcolor:#FFFF00]

11​

[/td][td]

1​

[/td][td]

7​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td=bgcolor:#FFFF00]

9​

[/td][td=bgcolor:#FFFF00]

10​

[/td][td=bgcolor:#FFFF00]

11​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td]

8​

[/td][td=bgcolor:#FFFF00]

9​

[/td][td=bgcolor:#FFFF00]

10​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]


Select all the range and in the CF put the following:


[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =IF(A1<>1,OR(A1=LARGE(A$1:A$7,1),A1=LARGE(A$1:A$7,2),A1=LARGE(A$1:A$7,3)))[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

To simplify Caribeiro's formula a little:

=AND(A1<>1,A1>=LARGE(A$1:A$7,3))

 

[Marcelo Branco]

What is the expected (desired) outcome with this data sample


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

A​

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

1​

[/TD]
[TD]

0,8​

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

2​

[/TD]
[TD]

0,5​

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

3​

[/TD]
[TD]

0,1​

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

4​

[/TD]
[TD]

0,2​

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

5​

[/TD]
[TD]

1​

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

6​

[/TD]
[TD]

0,3​

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

7​

[/TD]
[TD]

1​

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


Option 1

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

A​

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

1​

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

0,8​

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

2​

[/TD]
[TD]

0,5​

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

3​

[/TD]
[TD]

0,1​

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

4​

[/TD]
[TD]

0,2​

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

5​

[/TD]
[TD]

1​

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

6​

[/TD]
[TD]

0,3​

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

7​

[/TD]
[TD]

1​

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



Option 2 (disregard the 1s to get the third Large)

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

A​

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

1​

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

0,8​

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

2​

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

0,5​

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

3​

[/TD]
[TD]

0,1​

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

4​

[/TD]
[TD]

0,2​

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

5​

[/TD]
[TD]

1​

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

6​

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

0,3​

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

7​

[/TD]
[TD]

1​

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


Please, clarify.

M.