Results 1 to 5 of 5

Thread: Pull top 3 values and Row Headings

  1. #1

    Post Pull top 3 values and Row Headings



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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,

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    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.


  3. #3

    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
    Attached Files Attached Files

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    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.


  5. #5
    Well that worked like a charm!

    Thanks!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •