Google-sheets – Highlight second highest number in Google Sheets row

conditional formattinggoogle sheets

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.

Best Answer

Short Answer

Try

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

Explanation

RANK

Returns the rank of a specified value in a dataset

Related Solutions

Google-sheets – Getting the second, third or fourth largest elements

Apply this Custom formula is:

=or(I13=large(unique(I$13:I$41),2),I13=large(unique(I$13:I$41),3),I13=large(unique(I$13:I$41),4))

to Range: I13:I41 and change ColumnI references throughout to E for a separate rule.

Note that this applies to the second, third and fourth highest values however many instances of each there may be. So for 10,9,9,9,8,8,7,7,7,7,6 all but the first and last would be highlighted.

Google-sheets – Conditionally formatting the minimum value in a row

Sooo close (I think!). Please try:

=D3=min($D3:$L3)

=min(D3:L3) returns a value but CF needs a TRUE to trigger. So we check "the current cell" (D3) to see whether its value matches that of the minimum in the range D3:L3. If it does we trigger the formatting.

I say "the current cell" because quite which that is depends on the Range: selection. Here it is the same as that from which the minimum value is selected (ie D3:L3). CF has a kind of rastering feature, equivalent to checking whether to trigger the first cell in the Range: (and if TRUE, doing so) then automatically stepping on the E3, F3 cell (and if TRUE, doing so) etc until reaching the end of Range:.

(I have no idea what the actual sequence is but since all done with one click don't see that matters much.)

Without anchors (the $s in $D3:$L3) the minimum range also would auto adjust during the process, so D3 would be tested against D3:L3 but E3 against E3:M3, and so on. (This particular question, where the MIN range and Range: are the same, may not however be the most suitable one for explaining the anchors!)