Retrieving the Most Recent Values

multifidus

New Member
Joined
Feb 2, 2023
Messages
14
Office Version
  1. 365
Platform
  1. MacOS
I am building a database that has all of the statistics for each NBA player this season. Each team has their own workbook (example: LosAngelesLakersPlayers.xslx) and each player has their own sheet (example: James_LeBron), with each game in a single row, with each new game being added at the bottom row. I have a formula that is showing the average values for all the main categories for every player on a single spreadsheet.

I want to add separate columns that shows a player's 5 most recent results for a given statistic. In other words, I would like it to pull from the player's spreadsheet and display how many assists they got in the each of the last five games (not an average of the last five), separated by commas. I am not sure what function would be the best way to start as the spreadsheets update whenever the player had a new game.

Any advice/suggestions would be greatly appreciated.

Thank you!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Just curoius.. why do you need to have a workbook for each team and sheet for each player?
My suggestion is to keep everything in one workbook export reports regarding team or player stats as you need them.
You could build dashboards/charts/pivot tables for the teams and players on the other sheets in the workbook.

Just my two cents.

But to get most recent values you could use the LARGE function to the 5 Largest dates.
 
Upvote 0
Just curoius.. why do you need to have a workbook for each team and sheet for each player?
My suggestion is to keep everything in one workbook export reports regarding team or player stats as you need them.
You could build dashboards/charts/pivot tables for the teams and players on the other sheets in the workbook.

Just my two cents.

But to get most recent values you could use the LARGE function to the 5 Largest dates.
Thanks for the suggestion of Large I will read up on the function!

I have separate sheets because I am running a web query to get each player's stats updated daily. I could probably combine them each into a single large workbook. I will definitely look into that.

Thanks again!
 
Upvote 0
Just curoius.. why do you need to have a workbook for each team and sheet for each player?
My suggestion is to keep everything in one workbook export reports regarding team or player stats as you need them.
You could build dashboards/charts/pivot tables for the teams and players on the other sheets in the workbook.

Just my two cents.

But to get most recent values you could use the LARGE function to the 5 Largest dates.
I am not having luck using the LARGE function. I can get it to return the most recent date, but I cannot get it to then return the value of a cell in the corresponding row of that date. Do I nest this within another function? I tried using it within INDEX and VLOOKUP but I could not seem to get it right. Thank you!
 
Upvote 0
Another option would be to use the TAKE function (in Excel 365). If you turn your data into a table then when you update the date your formula will update.
Example of TAKE and FILTER / LARGE function.

Book2
ABCD
1Player1With TAKE function
2DateassistsLast 5
31/2/202327, 45, 12, 14, 45
41/5/20236
51/8/20232With Large function
61/11/202357, 45, 12, 14, 45
71/14/20238
81/17/20239
91/20/20237
101/23/202345
111/25/202312
121/30/202314
132/2/202345
Sheet3
Cell Formulas
RangeFormula
D3D3=TEXTJOIN(", ",,TAKE(Table2[assists],-5))
D6D6=TEXTJOIN(", ",1,FILTER(Table2[assists],Table2[Date]>=LARGE(Table2[Date],5)))
 
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,122
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top