non-progressive formula copying
 

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,

non-progressive formula copying
 

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jgraulau" wrote:
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,

non-progressive formula copying
 

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

"jgraulau" wrote in message
...
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,