Highlight second highest number in Google Sheets row

Question

I am attempting to use conditional formatting to highlight the second highest number in a row in Google Spreadsheets. I've set it to "format cells if custom formula is", applied to range "CO27:DB27" and I've tried the following;

=large(unique(CO$27:DB$27),2)
=LARGE(CO27:DB27, 2)
=INDEX(SORT(UNIQUE(CO27:DB27),1,FALSE),1,0)

These highlighted everything.

=LARGE(CO27:DB27, 3)

This highlighted everything BUT my second highest number and changing the 3 to a 4 then unhighlighted the cell next to it as well, which was not a high number at all. Since my highest number is in DB27, 3 un-highlighted it, 4 unhighlighted it and DB26.

=$CO27:$DB27=max($CO27:$DB27)

This is what I'm using to highlight the highest.

Answer 1

Short Answer

Try

=RANK(CO27,$CO$27:$DB$27,0)=2

Explanation

RANK

Returns the rank of a specified value in a dataset

Answer 2

Just to make this question finally answered the way original poster wants it to be:

To get the second largest value(s) in the set of values on a range of sheet, ignoring duplicate values.

You can use this formula:

=EQ(CO27,LARGE(UNIQUE(CO$27:DB$27),2))

That is, the function the original poster pasted was almost correct, we just need to add the equality to current cell.

Why is this so?

Because the function LARGE returns the value of the second largest number in the list. But what we want in the conditional formatting is either 1 if we want that cell to be formatted, or 0 if we want that cell to not be formatted. Therefore we need another comparison to make it true if and only if the value of the cell is the same as the second largest number.

Alternative ways

1. You can also use format cells if equals to instead using format cells if custom formula is, which is what the EQ is doing in my answer above
2. You can also use the similar format to what you use for max: =$CO27:$DB27=LARGE(UNIQUE(CO$27:DB$27),2)