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




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

 

[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