Extracting the top 5 maximum values in excel

Question

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

User · Accepted Answer

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

User · Answer

To my mind the case for a PT  as  Nathan Fisher  is a  no brainer   but I would add a column to facilitate ordering by rank  up or down      OPS is entered as VALUES  Sum of  twice so I have renamed the column labels to make clearer which is which  The PT is in a different sheet from the data but could be in the same sheet   Rank is set with a right click on a data point selected in that column and Show Values As    and Rank Largest to Smallest  there are other options  with the Base field as Player and the filter is a Value Filters  Top 10    one     Once in a PT the power of that feature can very easily be applied to view the data in many other ways  with no change of formula  there isn t one     In the case of a tie for the last position included in the filter both results are included  Top 5 would show six or more results   A tie for top rank between just two players would show as 1 1 3 4 5 for Top 5

User · Answer

Put the data into a Pivot Table and do a top n filter on it

User · Answer

VLOOKUP LARGE A1 A10 ROW    A1 B10 2 0    Type this formula in first row of your sheet then drag down till fifth row     its a simple vlookup  which finds the large value in array  A1 A10   the ROW   function  gives the row number  first row   1  second row  2 and so on  and further is the lookup criteria   Note  You can replace the ROW   to 1 2 3 4 5 as requried   if you have this formula in other than the 1st row  then make sure you subtract some numbers from the row   to get accurate results   EDIT  TO check tie results  This is possible  you need to add a helper column to the sheet  here is the link  Do let me know in case things seems to be messy

User · Answer

There 3 functions you want to look at here    LARGE - Returns the k-th largest value in a data set  INDEX - Returns a value or the reference to a value from within a table or range  MATCH - The MATCH function searches for a specified item in a range of cells  and then returns the relative position of that item in the range    I ran a sample in Excel with your OPS values in Column B and Players in Column C  see below      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

[excel] Extracting the top 5 maximum values in excel

Examples related to excel