# Average of x number of weeks data

#### Scott Fergus

##### New Member
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.

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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)))

Brilliant, works a treat, thank you

Replies
3
Views
75
Replies
4
Views
268
Replies
2
Views
276
Replies
6
Views
181
Replies
1
Views
77

1,203,522
Messages
6,055,900
Members
444,832
Latest member
bgunnett8

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back