# Average last seven entries in a row

#### SimonHughes

##### Active Member
Hello, I need a formula that will automatically update the last seven entries in a row of data. Data is added on a daily basis so today I need the average of C4 to I11 and tomorrow from C5 to I12

Using Excel 2016

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

#### shaowu459

##### Well-known Member
Book1
ABCDEFGHIJKLMNOPQ
1
2
3
411111111.19
51111111
61111111
71111111
81111111
91111111
101111111
111111111
121111119
13
Sheet1
Cell Formulas
RangeFormula
K4K4=AVERAGE(OFFSET(INDEX(C:I,LOOKUP(1,0/(C:C<>""),ROW(C:C)),),,,-6,))

#### SimonHughes

##### Active Member
Thanks but this formula is static. As I enter the data the new column the formula must shift as well. So the formula must give the average of C4 - I4 today, D4 to J4 the tomorrow day, E4 to K4 the following day and so on.
Sorry for not making this clear.

#### shaowu459

##### Well-known Member
Book1
ABCDEFGHIJKL
1
2
3
4111111112
5
6
71.166667
8
9
Sheet1
Cell Formulas
RangeFormula
D7D7=AVERAGE(OFFSET(INDEX(4:4,COUNTA(4:4)+2),,-5,,6))

Book1
ABCDEFGHIJKLM
1
2
3
41111111123
5
6
71.5
8
Sheet1
Cell Formulas
RangeFormula
D7D7=AVERAGE(OFFSET(INDEX(4:4,COUNTA(4:4)+2),,-5,,6))

#### SimonHughes

##### Active Member
That works perfectly, many thanks indeed. I just changed the array to read C4:ZZ4 as I wanted the results in column B.
Once again, thanks for taking the time to help.

#### shaowu459

##### Well-known Member
That works perfectly, many thanks indeed. I just changed the array to read C4:ZZ4 as I wanted the results in column B.
Once again, thanks for taking the time to help.
You are welcome

Replies
7
Views
224
Replies
5
Views
109
Replies
1
Views
238
Replies
1
Views
188
Replies
4
Views
458

1,132,912
Messages
5,655,912
Members
418,250
Latest member
Jebacmakro

### 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