Pull top 3 values and Row Headings

vguida

New member
Joined
Jul 14, 2014
Messages
3
Reaction score
0
Points
0
Players
AtBats
Batting Average
Mike
20
.325
Dave
22
.133
Vince
18
.210
Nick
25
.305
Alex
21
.266

I want to pull the Top 3 batter and their batting average. I can use the max/large functions, but cant get the index or offset to work.

Thanks,
 
Assuming your data, with headers, is in Sheet1, A1:C6.

Now, assuming the results are going to be in Sheet2, starting in A2... and also assuming you can have duplicate AtBats, you will need a way to pull the unique names and averages...

So first use the LARGE() function to pull the top scores.

In A2 of Sheet2:

=LARGE(Sheet1!$B$2:$B$6,ROWS(Sheet1!$A$2:$A2))

copied down to get the number of scores you want.

Then to get the Player names in B2:

=INDEX(Sheet1!$A$2:$A$6,SMALL(IF(Sheet1!$B$2:$B$6=A2,ROW(Sheet1!$B$2:$B$6)-ROW(Sheet1!$B$2)+1),COUNTIF(A$2:A2,A2)))

confirmed with CTRL+SHIFT+ENTER, not just ENTER and copy down.

Similar for Batting Averages, replacing the INDEXed range.
 
Player Name doesnt work

I've attached the actual excel file I'm trying to complete... I'm trying to get the TOP 3 (Player Names and Totals) for Columns C, J & L
 

Attachments

  • Batting Stats.xlsx
    12.5 KB · Views: 15
In N2 enter:

=INDEX($A$2:$A$13,SMALL(IF($C$2:$C$13=O2,ROW($B$2:$B$13)-ROW($B$2)+1),COUNTIF(O$2:O2,O2)))

confirm with CTRL+SHIFT+ENTER, then copy down to N4.

In N6 enter:

=INDEX($A$2:$A$13,SMALL(IF($J$2:$J$13=O6,ROW($B$2:$B$13)-ROW($B$2)+1),COUNTIF(O$6:O6,O6)))


confirm with CTRL+SHIFT+ENTER, then copy down to N8.

In N10:

=INDEX($A$2:$A$13,SMALL(IF($L$2:$L$13=O10,ROW($B$2:$B$13)-ROW($B$2)+1),COUNTIF(O$10:O10,O10)))

confirm with CTRL+SHIFT+ENTER, then copy down to N12.
 
Back
Top