Extracting the top 5 maximum values in excel
excel
Solution
Given a data setup like this:
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.
Problem
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.