Highlight second highest values, problem with ties

Hello everyone!

I've set up a competition with friends. The goal is to predict football scores. A point is given for every correct score. A part of the prizemoney is for the one with the most points and another (smaller) part for the second-highest amount of points. If there are persons with the same (highest or second-highest) points, the respective part is divided among them.

I want to highlight the persons who are in first (GREEN) and second (YELLOW) position (two different colors) with conditional formatting (for the first position I use =B7=MAX(B$7:B$21) formula in conditional formatting, no problem). How can I highlight the persons in second position? I tried with =B7=LARGE(B$7:B$21;2). This works, but only when the highest value is unique. If for example 2 persons have the same highest points (both 8 correct scores for example) (there is a tie), they are both highlighted as 'second place'. Can someone find a solution when a tie occurs?

How it should look like: (example):
Tim 8 (highlight) GREEN
Rick 6 YELLOW
Chris 8 GREEN
Pete 6 YELLOW
Dimi 4 no highlight


Thanks in advance! I would be very happy if someone can solve this
The excel (2010) file is attached. Sorry for the dutch version :s

grtz

I was just about to post the same thing (but without the extra bracket at the end )

=B7=LARGE(B$7:B$21,COUNTIF($B$7:$B$21,MAX($B$7:$B$21))+1)

Mission accomplished Thank you vandan_tanna and Cutter for your quick response and effective solution! Congratz to excelforum and its members!
grtz

Going further exploring the possibilities of excel, I was wondering if you can figure out this more complex option:
in combination of your solution mentioned above, following handlings would improve the spreadsheet:

Instead of just filling in 1 for a correct score and 0 for a wrong score manually in the 'field' C6:Z21, I would like to insert (before the start of the competition) all estimated scores of the participants in this field. As the competition goes on, I will fill in the correct scores in row 4 (C4:Z4). When the estimated score in the 'field' is correct, and in consequence identical to the respective score in row 4, this estimation should be highlighted. Consequently, column B (B4:B21) should sum up the correct (highlighted) scores. Finally, the same highlighting solution in column B from the initial thread above should be implemented (highlighting highest and second-highest scores, dealing with the tie-problem).

I hope it doesn't appear too complex?

I'm curious about discovering more on this subject, thanks in advance!
grtz

That all sounds doable.

should I open a new thread on this topic?

It's so closely related, I would say no. But you should upload a new sample showing what you now want to do.

in attachment a sample ('2nd plan')of how it should look like, to illustrate my last post

obviously the sample is an example for only 6 participants and 4 matches, just to clarify things

I inserted all your solutions in the spreadsheet, everything works! Thanks for your contribution and (very) quick responses
grtz