Average of x number of weeks data

[Scott Fergus]

Hi, I have 100 rows of data containing with 52 weeks of information (i.e. 100 rows and 52 columns). I would like to calculate an average of the last x number of weeks of data for cell in the array.
For example; if x=5 then for row 1, for week 52 it will average weeks 48-52, for week 51 it will average weeks 47-51, which is fairly straight forward except for two issues.
Issue 1: I need to change the number of weeks being averaged (x) on the fly, without having to change the formula i.e. the number of cells being averaged (x) is based on a value entered (x) in another cell.
Issue 2: How do I adjust the formula to take into account that the range of weeks in the first few weeks of the year are less than the number of weeks being averaged (x). I would still like the average to be calculated even if for example x=5, then for week 4 the formula only calculates the average of the first four weeks, etc.
Thanks in advance.

 

[konew1]

With A10 holding the required number of weeks.
This assumes your data starts in column B. You will need to adjust the Column()-1 to suit
=AVERAGE(OFFSET(B7,0,0,,-MIN($A$10,COLUMN()-1)))

 

[Scott Fergus]

Brilliant, works a treat, thank you