How to grab certain data for calculation

I have a table of 32 NFL teams (in rows) and columns for each week of the season. A formula takes a rating for each team from another Sheet for each game played and places it in this table, so columns will be filled from left to right as the season progresses. A "Season to Date Average" is easy to calculate using the =AVERAGE function in a fixed column to the right of all these weeks since it ignores blank columns (unplayed games) to calculate an average. But how would I put an Average Rating in another column to the right, which could be based on a number (which is up above, say, the heading of the column) which is the number of recent weeks to include in the average? For instance, at times I may want the average rating from only the most recent 3 weeks; other times the most recent 5 weeks. With the latest week of data being in a different column every week as we go through the season, I don't know how to build a formula which would capture the right set of weekly ratings to calculate an average rating. I haven't done any programming or VBA in Excel yet and just know the basic functions. Also after the average is calculated for a recent set of weeks, I will use the SORT function to reorder the rows (teams), highest to lowest, note the results, then analyze performance for another set of recent weeks. Thank you.

Thank you!! That suggestion worked very well and I'm up and going.

Solved and all good