0

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.

  • I added the conditional-formatting tag as per your suggestion, I didn't realise there was one. Yes, "not already highlight" with CF. Optimally I'd like to get the second, third and fourth highest elements from I13:I41 and E13:E41 separately. I hadn't though of using top 4 and top 1 as different rules though no, I'll try that now. – Andrew B Jul 25 '14 at 1:30
1

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.

1

In addition to pnuts solution, this conditional format custom formula may be applied to the same range:

=COUNTIF(I$13:I$34,">"&N(I13))<4

For 10, 9, 9, 8, 8, 7, this will return 10, 9, 9, 8, 8 (ie the tie won't be broken for "last place").

To break that tie, and therefore return no more than 4 values:

=AND(COUNTIF(I$13:I$34,">"&N(I13))<4,COUNTIF(I$13:I13,">="&N(I13))<=4)

  • 1
    Yes I probably did misread it. If the OP gives some clarification I will edit/delete answer as appropriate. – AdamL Jul 31 '14 at 23:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.