Average last seven entries in a row

SimonHughes

Active Member
Joined
Sep 16, 2009
Messages
389
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

Many thanks in advance
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
534
Office Version
  1. 365
Platform
  1. Windows
please see if this helps:
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
Joined
Sep 16, 2009
Messages
389
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
Joined
Apr 26, 2018
Messages
534
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 16, 2009
Messages
389
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
Joined
Apr 26, 2018
Messages
534
Office Version
  1. 365
Platform
  1. Windows
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 :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,129,489
Messages
5,636,626
Members
416,931
Latest member
pattichis

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top