I'm trying to calculate the average of the previous 3 and 10 instances, but I don't want to change the order

[PETERFT73]

I have what I consider to be an enormous dataset (approx 150k rows) of NBA player game logs. I'm trying to show the average of the player's previous 3 and 10 game performances. Where I think the real difficulty came up for me is that I would rather not re-arrange the data by changing it to alphabetical order by player name (currently in chronological order). With days off, it can be over 100 rows between a given player's entries. What I'm ultimately after is to change the whole set so that on a given day none of that day's actual statistics appear, but rather the past 3 day/10 day averages of those statistics. I tried to paste in a few rows down below in case that might help someone better see what I'm up against here.
Thanks in advance to any of you who offer some help!
Peter

gmDate	teamAbbr	teamLoc	teamDayOff	playDispNm	playStat	MIN	POS	HGT	WHT	BDAY	playPTS	playAST	playTO	playSTL	playBLK	playPF	playFGA	playFGM	playFG%	play2PA	play2PM	play2P%	DK	play3PA	play3PM	play3P%	playFTA	playFTM	playFT%	playORB	playDRB	playTRB	opptAbbr	opptConf	opptDiv	opptLoc	opptRslt	opptDayOff
10/30/12	WAS	Away	0	A.J. Price	Starter	29	PG	74	195	10/7/86	7	6	1	0	0	1	13	2	0.1538	4	0	0	19	9	2	0.2222	1	1	1	1	1	2	CLE	East	Central	Home	Win	0
10/30/12	WAS	Away	0	Trevor Ariza	Starter	25	SG	80	200	6/30/85	9	4	0	3	2	0	8	3	0.375	4	1	0.25	29.75	4	2	0.5	2	1	0.5	1	2	3	CLE	East	Central	Home	Win	0

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col span="28"></colgroup><tbody>
</tbody>

 

[Rijnsent]

Hi Peter,
starting with structure: I'd keep the original data and make new columns/sheets with the 3d/10d averages, don't overwrite it. Having said that, you could use AVERAGESIFS to achieve what you want, see e.g. this for a tutorial: https://exceljet.net/formula/average-by-month . The main issue will be performance, as 150k of rows of formulas will take a very long time to recalculate. So what you could do is not using the whole dataset in calculations, but a range which will include what you're looking for. That, and/or only calculating 10k rows at once or a macro to do that for you.
Hope that helps,
Koen