+ Reply to Thread
Results 1 to 6 of 6

Formula for finding the average scores for a player in last 3 games

  1. 12-31-2016, 01:35 PM #1
    philozelda
    philozelda is offline
    Registered User
    Join Date
    02-21-2014
    Location
    Connecticut, U.S.A.
    MS-Off Ver
    Excel 2010
    Posts
    52

    Formula for finding the average scores for a player in last 3 games

    Hey everyone - I am looking for a formula solution that will help me find the average score for a player in his last 3 games. To do this, I have to consider 3 different columns:

    playerName Date Score
    russell westbrook 12/22/2016 60
    james harden 12/22/2016 55
    russell westbrook 12/23/2016 42
    james harden 12/23/2016 42
    russell westbrook 12/24/2016 67
    james harden 12/24/2016 55
    russell westbrook 12/27/2016 48
    james harden 12/27/2016 72
    russell westbrook 12/29/2016 42

    In the example table, I calculated the last 3 game scores for Russell Westbrook as: 67,48, and 42 (12/24, 12/27, and 12/29). His average for those 3 games is 52.33. As the season goes on, I always want to be able to calculate the average for the most recent 3 games. I would do this calculation off on another sheet adjacent to the player name, so Ideally I'd like to stay away from using help columns, etc.

    Does anyone know how to create such a formula?
    Reply With QuoteRegister To Reply
  2. 12-31-2016, 01:42 PM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Formula for finding the average scores for a player in last 3 games

    Assuming the name is in F1, use this:

    =AVERAGE(INDEX(C:C,N(IF(1,LARGE(INDEX((A$2:A$10=F1)*(ROW(A$2:A$10)),0),{1,2,3})))))
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
  3. 12-31-2016, 03:37 PM #3
    philozelda
    philozelda is offline
    Registered User
    Join Date
    02-21-2014
    Location
    Connecticut, U.S.A.
    MS-Off Ver
    Excel 2010
    Posts
    52

    Re: Formula for finding the average scores for a player in last 3 games

    Beautiful! Worked very well.

    Thx, Glenn!
    Reply With QuoteRegister To Reply
  4. 12-31-2016, 08:31 PM #4
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Formula for finding the average scores for a player in last 3 games

    If this is a list that you are going to add to vertically down the worksheet, this minor amendment to Glenn's formula will accommodate data as you add it.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    All that this does is to make the references to column A dynamic which means that you don't have to make amendments to the formula as you add data. If you are not going to add data making the ranges longer then this doesn't matter.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W
    Reply With QuoteRegister To Reply
  5. 12-31-2016, 08:57 PM #5
    Logit
    Logit is online now
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    7,479

    Re: Formula for finding the average scores for a player in last 3 games

    .
    .
    .
    What am I missing ?
    .
    .
    .

    Average.jpg
    Reply With QuoteRegister To Reply
  6. 12-31-2016, 09:12 PM #6
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Formula for finding the average scores for a player in last 3 games

    This is to calculate the average of the last 3 values. In your example you have only 2 John Doe entries and not the 3 that are required. To eliminate the situation of only 1 or 2 entries this will do it.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    This might be a little faster. I don't have enough data to test but on limited data it is marginally faster on average.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. scores posted on 'games' sheet, sent to 'matches & games' sheet, find winner, ...
    By matcutter in forum Excel General
    Replies: 4
    Last Post: 03-31-2016, 11:43 PM
  2. Trying to Pick 10 lowest golf scores from a list of scores and average for Handicaps
    By 1wxman1 in forum Excel - New Users/Basics
    Replies: 8
    Last Post: 12-20-2014, 04:37 PM
  3. [SOLVED] formula to get the average scores per distinct names
    By albert28 in forum Excel General
    Replies: 2
    Last Post: 02-03-2014, 02:33 AM
  4. [SOLVED] Top scores w/ corresponding player, also with a tie
    By madman1734 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-15-2013, 11:40 AM
  5. [SOLVED] Trimming text from a sheet with teams and scores of games
    By mrvp in forum Excel General
    Replies: 3
    Last Post: 09-10-2012, 03:53 PM
  6. Finding average study scores
    By Ness78 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-08-2012, 09:47 PM
  7. finding player and avg top 3 scores
    By xpostie in forum Excel General
    Replies: 6
    Last Post: 06-08-2012, 02:59 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1