+ Reply to Thread
Results 1 to 7 of 7

Help with formula showing avg of each person's top scores

  1. #1
    Registered User
    Join Date
    01-19-2015
    Location
    Dallas, TX
    MS-Off Ver
    2013
    Posts
    5

    Help with formula showing avg of each person's top scores

    Thanks in advance for your help. Usually I can figure stuff out when I'm stuck with some searches but I've run into a wall here.

    If in Column A I have NBA player names (say 20 of each guy) and in Column B I have the points they have scored in each game, is there a way to create a column with a formula that gives you the average of only each guy's top 5 scores? Thanks and let me know if that doesn't make sense.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Help with formula showing avg of each person's top scores

    It can be done with an pivot table.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    01-19-2015
    Location
    Dallas, TX
    MS-Off Ver
    2013
    Posts
    5

    Re: Help with formula showing avg of each person's top scores

    Thanks for the quick response. I will look into pivot tables and see if I can figure it out.

  4. #4
    Registered User
    Join Date
    01-19-2015
    Location
    Dallas, TX
    MS-Off Ver
    2013
    Posts
    5

    Re: Help with formula showing avg of each person's top scores

    So I've figured out how to make the pivot table but it is taking the average of all of each guy's scores. How do I get it to take the average of just the best 5 games from each guy? Thanks.

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help with formula showing avg of each person's top scores

    Here's how to do it using a formula.

    Due to space limitations I'm getting the average of the top 3 scores.

    Data Range
    A
    B
    C
    D
    E
    1
    Player1
    92
    Player1
    82.66667
    2
    Player1
    90
    Player2
    54
    3
    Player1
    66
    Player3
    65.33333
    4
    Player1
    27
    5
    Player1
    26
    6
    Player2
    69
    7
    Player2
    53
    8
    Player2
    40
    9
    Player2
    22
    10
    Player2
    18
    11
    Player3
    72
    12
    Player3
    69
    13
    Player3
    55
    14
    Player3
    48
    15
    Player3
    14
    16
    ------
    ------
    ------
    ------
    ------


    This array formula** entered in E1 and copied down:

    =AVERAGE(LARGE(IF(A$1:A$15=D1,B$1:B$15),{1,2,3}))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    If you want the top 5 just change this:

    {1,2,3}

    To this:

    {1,2,3,4,5}
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Registered User
    Join Date
    01-19-2015
    Location
    Dallas, TX
    MS-Off Ver
    2013
    Posts
    5

    Re: Help with formula showing avg of each person's top scores

    Thanks so much. I was able to get this to work.

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Help with formula showing avg of each person's top scores

    Good deal. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 8
    Last Post: 12-20-2014, 04:37 PM
  2. [SOLVED] Run calculations in template for 1 person, copy-paste results, repeat for next person
    By Geoff. in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-25-2014, 06:02 PM
  3. VBA to convert person-to-event into person-person
    By LuckyStrike in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-07-2014, 02:34 PM
  4. Transpose/Pivot multiple rows per person into 1 row per person with fixed columns
    By MaestroEnrique in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-13-2013, 06:35 AM
  5. how to add weekly scores w/o showing current scores
    By breal33 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-02-2007, 11:56 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1