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

conditional formattinggoogle sheets

I have tried the formula given here: Second highest value
which is the following =INDEX(SORT(UNIQUE(A1:A30),1,FALSE),1,0) however it will highlight everything that's not already highlighted.

I'm already using =LARGE() and I know that for the last argument changing the number will change how many it will apply to the conditional formatting.

The issue at hand is that using, for instance =LARGE(I10:I30, 3) will apply the background colour to the top 3 in my sheet.

So does anyone know of any other ways to get the second or third elements in my spreadsheet?

Example of my sheet for usage:

I13-I41 are all results of a formula similar to =(F13+H13)/G13

E13-E41 are all results of a formula similar to =((C13/B13) * 100)

These are the only two columns I need to format, but I would like to get the second highest element. Third and fourth highest I'd like to get as well.

Best Answer

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.

Related Topic