Finding the 1st, 2nd and 3rd highest scores

[admiraldick]

Hi everyone,

i play a fantasy football boardgame (Blood Bowl) in a league and am trying to put together a table that will collate each players performance in the league. it will contain obvious information like wins, losses, draws and league score, but will also contain information like goals scored, goals conceeded and the difference between the two.

i'd like to pick out the top three unique scores so that league members can quickly see who are the highest ranked players. i've spent a long time seaching the internet for an answer, but not found anything robust enough to answer this problem. the formula will have to cope with duplicate scores and negative scores. the table will look something like this:

Player	Score		1st	???
Matt	5		2nd	???
Phil	3		3rd	???
Rich	-3
James	5
Jo	-1
Colin	-1
Nigel	5
Kiran	-3
Alex	-5
Chris	3

can anyone think of a solution?

[Pete_UK]

Would you expect the top 3 scores here to be 5, 5 and 5 ?

If so, you can just use the LARGE function, with the second argument dependent on ROWS($1:1) even if your table does not start on row 1.

Hope this helps.

Pete

[olasa]

One option is to use a Pivot table, 'Top 3 filtered'
The other option (already mention above) is to use a formula
//Ola


Alt.1
Row label: Player, Values: Score
Click on header Row labels and choose 'Value Filters' and select 'Top 10...' and change to 3.

Alt.2 (one example)
The other option: already mention above is to use a formula (something like below) in column
C: =B2+ROW()*10^-10 --- helper column
E: =INDEX($A$2:$A$11;MATCH(LARGE($C$2:$C$11;ROWS($D$2:$D2));$C$2:$C$11;0)) --> Player name
F: =VLOOKUP(E2;$A$2:$C$11;2;0) --> Player score

[admiraldick]

thanks guys for your input. i was really looking for a way to find the top three unique scores (rather than individuals), so that players with those scores could be highlighted. thankfully, i've actually managed to find a solution myself. by adjusting a CSE formula i found else where that worked on a combination of LARGE and IF. check out my solution: