Retrieving the Most Recent Values

[multifidus]

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!

 

[awoohaw]

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.

 

[multifidus]

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!

 

[multifidus]

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!

 

[AhoyNC]

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
	A	B	C	D
1	Player1			With TAKE function
2	Date	assists		Last 5
3	1/2/2023	2		7, 45, 12, 14, 45
4	1/5/2023	6
5	1/8/2023	2		With Large function
6	1/11/2023	5		7, 45, 12, 14, 45
7	1/14/2023	8
8	1/17/2023	9
9	1/20/2023	7
10	1/23/2023	45
11	1/25/2023	12
12	1/30/2023	14
13	2/2/2023	45
Sheet3

Cell Formulas
Range	Formula
D3	D3	=TEXTJOIN(", ",,TAKE(Table2[assists],-5))
D6	D6	=TEXTJOIN(", ",1,FILTER(Table2[assists],Table2[Date]>=LARGE(Table2[Date],5)))