Extracting the top 5 maximum values in excel

20

I have an excel file with one column corresponding to the player's name and the other column corresponding to the baseball statistic OPS.

 OPS        Player
    1.000   player 1
    5.000   player 2
    3.000   player 3
    1.000   player 4
    ---     player 5
    4.000   player 6
    1.000   player 7
    ---     player 8
    1.000   player 9
    ---      player 10
    1.333   player 11
    1.000   player 12
    2.000   player 13
    ---     player 14
    ---     player 15
    ---     player 16
    1.500   player 17
    3.500   player 18
    1.500   player 19
    ---     player 20
    1.000   player 21
    1.000   player 22
    0.000   player 23
    0.000   player 24
    0.500   player 25
    0.000   player 26
    0.667   player 27

Now, in excel, I need to figure out how to create a formula that returns a column of the names of the players with the top 5 OPS value. Thus, I would like for the query to return a 5 x 1 column vector in excel. What cell formula could I use to achieve this?

Also, given that their will be repeating values of OPS, I need the expression to be robust against ties.

This question is tagged with excel

~ Asked on 2013-08-30 08:45:42

The Best Answer is


26

Given a data setup like this:

Top 5 by criteria

The formula in cell D2 and copied down is:

=INDEX($B$2:$B$28,MATCH(1,INDEX(($A$2:$A$28=LARGE($A$2:$A$28,ROWS(D$1:D1)))*(COUNTIF(D$1:D1,$B$2:$B$28)=0),),0))

This formula will work even if there are tied OPS scores among players.

~ Answered on 2013-08-30 19:12:28


15

There 3 functions you want to look at here:

I ran a sample in Excel with your OPS values in Column B and Players in Column C, see below:

Excel sample

  • In Cells A13 to A17, the values 1 to 5 were inserted to specify the nth highest value.
  • In Cell B13, the following formula was added: =LARGE($B$2:$B$11, A13)
  • In Cell C13, the following formula was added: =INDEX($C$2:$C$11,MATCH(B13,$B$2:$B$11,0))
  • These formulae get the highest ranking OPS and Player based on the value in A13.
  • Simply select and drag to copy these formulae down to the next 4 cells which will reference the corresponding ranking in Column A.

~ Answered on 2013-08-30 09:10:41


Most Viewed Questions: