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 related to
excel
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.
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:
=LARGE($B$2:$B$11, A13)
=INDEX($C$2:$C$11,MATCH(B13,$B$2:$B$11,0))
Put the data into a Pivot Table and do a top n filter on it
=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....
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.
Source: Stackoverflow.com