+ Reply to Thread
Results 1 to 13 of 13

Display the Average of the Previous 10 Games Dynamically

  1. #1
    Registered User
    Join Date
    02-11-2023
    Location
    Cincinnati, OH
    MS-Off Ver
    Office 365
    Posts
    6

    Display the Average of the Previous 10 Games Dynamically

    I am building a statistics workbook to track each NBA player. As a part of that I have a web query running for each player from Baseketball-Reference. As you will see in the spreadsheet I attached each player has their own sheet and the stats will automatically update with new data in the bottom column.

    I have a "master" sheet that has the averages for each of the different statistics for each player and updates accordingly (in the example I attached it is the "BOS Total" sheet).

    The next thing I have been trying to build is to have a more dynamic sheet where I can select any player from a drop-down list and if that player is selected it will return the average for ONLY the last 10 games (ignoring games that the player was inactive or did not play).

    The sheet "Last10Avgs" has an example of what I am trying to do.

    Thank you in advance for reading/your help!
    Attached Files Attached Files

  2. #2
    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: Display the Average of the Previous 10 Games Dynamically

    Mmm. There are lots of things I don't like about your layout. The data starts in different rows, the sheet names do not match the player names, you have stuck repeat header rows in the middle of the data (not needed... it breaks the flow and if required for printing, vastly superior alternatives are available).

    I don't know what VERSION of O365 you are using, but if it is reasonably up to date, this works:

    =LET(A,COLUMN()+7,B,N(+INDIRECT("'"&TEXTAFTER($B2," ")&"_"&TEXTBEFORE($B2," ")&"'!"&ADDRESS(1,A)&":"&ADDRESS(500,A))),IFERROR(AVERAGE(TAKE(FILTER(B,B>0),10)),"<10 Games"))
    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

  3. #3
    Registered User
    Join Date
    02-11-2023
    Location
    Cincinnati, OH
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Display the Average of the Previous 10 Games Dynamically

    Hi Glenn,

    Thank you for taking the time to reply to my post.

    Unfortunately, the data stats in different rows for each player because of the website I am getting the daily updated stats from (basketball reference) has their HTML tables configured differently based on the player's stats. This will push down the main the starting point of the headers for the main table. I am not sure how to change that as this is a web query.

    I have since updated the sheet names to match the player's name for ease.

    Thank you again!

  4. #4
    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: Display the Average of the Previous 10 Games Dynamically

    The formula I suggested allows collection of data from row 1` to 500. Is that enough? Did it work, or not in MY file. If so, repost YOUR file and I can tweak it, if needed.

    If all is good and you've done it yourself, then....


    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

  5. #5
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Display the Average of the Previous 10 Games Dynamically

    If you don't mind to use helper cells and INDIRECT (that's make Excel calculate slower).

    Please Insert sheet name before player in 'BOS Total'
    Insert 2 rows above 'Last10Avgs' and

    C1 = 10
    D1 = C1+1 and so on.

    C2
    =SUBSTITUTE(ADDRESS(1,C1,4),"1","")

    A4
    =INDEX('BOS Total'!$A$2:$A$16,MATCH(B4,'BOS Total'!$B$2:$B$16,0))

    C4
    =IF($B4<>"",IFERROR(AVERAGE(INDIRECT("'"&$A4&"'!"&C$2&$AA4&":"&C$2&$AB4)),""),"")

    Helpers
    X4
    =MATCH("G",INDIRECT("'"&A4&"'!B:B"),0)
    Y4
    =MAX(INDIRECT("'"&A4&"'!B"&X4&":B100000"))
    Z4
    =MAX(1,Y4-9)
    AA4
    =MATCH(Z4,INDIRECT("'"&A4&"'!B"&X4&":B100000"),0)+X4-1
    AB4
    =MATCH(Y4,INDIRECT("'"&A4&"'!B"&X4&":B100000"),0)+X4-1

    Note : The formula will average last 10 (or less).

    Regards.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    02-11-2023
    Location
    Cincinnati, OH
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Display the Average of the Previous 10 Games Dynamically

    Hi Glenn,

    Sorry i was out of town and just got a chance to get back to you.

    I checked the updated workbook you posted a little closer and it looks like it is not pulling the appropriate date. I ran a test with Grant Williams from the drop-down window, and his stats are all off and not the average of the last 10 games. I am wondering if it is because the column headers on the Last10Avgs sheets do not line up exactly with those for each player? For example it looks like the column for points "PTS" is column T in the Last10Avgs, but is column AB for each player. I have attached the spreadsheet again to see if you have any time to check it out again and see if you can help with this issue.

    Thank you again!
    Attached Files Attached Files

  7. #7
    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: Display the Average of the Previous 10 Games Dynamically

    The problem was column S in the individual sheets. FT%. You wanted it excluded, but had not said so. Now it is excluded.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    02-11-2023
    Location
    Cincinnati, OH
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Display the Average of the Previous 10 Games Dynamically

    Unfortunately, it is not correctly pulling the previous ten games. I checked with Grant Williams again and looked under PTS and TRB by first calculating the average on his individual sheet (they just played last night so I had to make sure to update the data first). The TRB average should be 5.3 and the PTS average should be 6.5 (but it says 4.4 and 9.5, respectively on the last10avg sheet).

    I really appreciate you taking the time to look at this problem and I apologize if I have not been as clear you would like, but if you have any time to look at this again I would really appreciate it. I completely understand if you are no longer interested though.

    Either way, cheers!

    BostonCelticsPlayers (2).xlsx

  9. #9
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Display the Average of the Previous 10 Games Dynamically

    Please try post #5, it's may help.

    Regards.

  10. #10
    Registered User
    Join Date
    02-11-2023
    Location
    Cincinnati, OH
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Display the Average of the Previous 10 Games Dynamically

    Hi Menem,

    So sorry that I did not reply to you earlier!

    This is a very useful sheet and it definitely shows me the last ten game averages (THANK YOU!). Is there a way to add a drop down list of the players that will show me these stats dynamically, instead of listing all of the players? I want to build this out to eventually have all of the players in the league on the drop down so it would be great if I could just look at one player at a time if I wanted to instead of having to scroll through a sheet with all of the players.

    Thank you again, I really appreciate you taking the time to put this together!

  11. #11
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Display the Average of the Previous 10 Games Dynamically

    In the 'Last10Avgs' sheet , they're already dropdown based from player name in 'BOS Total'

    Regards.

  12. #12
    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: Display the Average of the Previous 10 Games Dynamically

    OK... Back again. I've spent a long time at 40,000 feet and recovering from jet lag.

    TRB. Simple explanation. I'm an idiot. I was taking the FIRST 10 results, not the last.

    PTS. Simple explanation. You didn't explain clearly. Now that you have provided expected results, I see that scores of zero are to be excluded.

    See if it's OK now.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    02-11-2023
    Location
    Cincinnati, OH
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Display the Average of the Previous 10 Games Dynamically

    Thank you Glenn that works!

+ 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. [SOLVED] Finding Average for Last 5/10 games closing line
    By showtimesaints in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-14-2022, 06:31 AM
  2. [SOLVED] How to calculate average score of games
    By colemic in forum Excel General
    Replies: 5
    Last Post: 11-09-2022, 03:05 AM
  3. Replies: 3
    Last Post: 11-09-2021, 05:24 PM
  4. previous month and average for previous last 3 months
    By mz_h in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-03-2020, 11:39 AM
  5. Replies: 2
    Last Post: 04-17-2018, 09:45 PM
  6. Replies: 5
    Last Post: 12-31-2016, 09:12 PM
  7. [SOLVED] Display last 10 games FORM win/draw/loss
    By alsan in forum Excel General
    Replies: 5
    Last Post: 09-06-2016, 06:40 AM

Tags for this Thread

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