non-progressive formula copying

[Guest]

Hello!
I am trying to peform a time series data analysis in excel. This is the
problem that I'm having.
I have a continuous time series data in Column A of a workseet (about 10,000
values). In column B I am trying to calculate the average of 7
non-overlapping cells and repeat this process for the entire time series.
For instance, cell B1 should contain the following formula: =AVERAGE(A1:A7),
cell B2 should be =AVERAGE(A8:A14), cell B3 should be =AVERAGE(A15:21) and so
on. This there an easy way of achieving this in excel without going into
macro writing and all that stuff?
Thanks,

 

[Guest]

One way ..

In B1:
=AVERAGE(OFFSET(INDIRECT("A"&ROWS($1:1)*7-6),,,7))
Copy B1 down as far as required, eg to B1430 thereabouts, if you have ~10k
rows of source data in col A.

 

[David Biddulph]

=AVERAGE(OFFSET(A$1,7*(ROW()-1),0,7))