Excel question

[jscol]

I have a nba totals model that has gone pretty decently, but the next step to implement is to be able to take the stats from the last 5 or so games from the team and work them in.

So I have each game listed with all the stats I need for my formula, efficiency, pace etc...

Is there a way I can set up a function to find say last 5 home games for Knicks and their opponents?

What is the best way I could set this up?

Thanks

[apom12]

I'll be honest - get friendly with Microsoft Access, and you will thank me. I took a Microsoft access class at college and it's Bern my best friend when handicapping

[jscol]

thanks for being honest

[Data]

I have a nba totals model that has gone pretty decently, but the next step to implement is to be able to take the stats from the last 5 or so games from the team and work them in.

So I have each game listed with all the stats I need for my formula, efficiency, pace etc...

Is there a way I can set up a function to find say last 5 home games for Knicks and their opponents?

What is the best way I could set this up?

Thanks

Sort your spreadsheet by home team name and then by date. Create a new column that lists a home game number (1 to 41) for the home team. Create a new column that calculates whatever stat you need using data from 5 rows above if the home game number is more than 5. Copy and paste the formula somewhere outside for future re-use. Select the new stats column, Copy and Paste Special, Paste Values. Optional, record this as a Macro.

[djiddish98]

I frequently use pivot table for something like this - I setup a similar approach to Data - create a column that is the formula [game date] - [season start date], so the higher the number, the more recent the game. Create a pivot table and then filter for top 5 or something along that line (also filter for home/away as a separate field) and you'll have your numbers.

Refreshing the pivot once you load in the new data will roll the data in as opposed to copying+pasting.