Highlighting the SECOND largest number in a column ?

[Aitch]

I'm using Conditional Formatting with the MAX formula:

=A1=MAX($A$2:$A$222)

to find and highlight the highest value in the A column range


How can I do the same to highlight the second largest value in the column?


Thanks for the help!

 

[Tetra201]

Try

=A2=LARGE($A$2:$A$222,2)

 

[vcoolio]

Post deleted as the same answer was already supplied by Tetra.

 

[Phuoc]

Or try:

=A1=MAX(($A$1:$A$222<>MAX($A$1:$A$222))*$A$1:$A$222)

10
9
10
10

 

[Aitch]

Great!

How do I use this with an ArrayFormula so it checks for the highest in blocks of 10 down the column?

A1-A10
A11-A20 etc

This doesn't seem to work =ArrayFormula(A2=LARGE($A$2:$A$12,2))