Hello David,
Thank you so much for the formula, it worked very well, now I just have to
experiment around to suit my other requirements e.g previous 5-,4-,3- months
and then ahead 3-,4-months etc. My problem with the XL help is they only give
the simple examples.

Because of that can I just ask you from your fomula:
=AVERAGE(OFFSET(C$2,3*(ROW()-ROW(D$8)),0,6,1))
what 0,6,1 at the end refer to. I think I know 6 refers to the 6 months to
be averaged but I don't know how 0 and 1 contribute to the formula.

I do acknowlege that I did not explain my problem that well.

Thanks again for your great help.

Speedy




"David Biddulph" wrote:

The way you showed it in your example, D2 was "mean of 1930(j,a,s,o,n,d)"
and D3 was "mean of 1930(o,n,d),1931(j,f,m)", so I had assumed that D4 was
where you wanted 1931 (jfm amj), which is your =average(c2:c7). If, instead
of the way you had it laid out in your original question, you now want that
in D8 instead of D4, then just change my formula from
=AVERAGE(OFFSET(C$2,3*(ROW()-ROW(D$4)),0,6,1)) to
=AVERAGE(OFFSET(C$2,3*(ROW()-ROW(D$8)),0,6,1)) and put it in D8.

As for explanation of the formula, the functions used are all standard Excel
functions, and Excel help explains the syntax and gives examples. AVERAGE()
is one that you probably understand already, but OFFSET() and ROW() are both
functions that you can find in Excel help.
The simple story is that when placed in cell D8, the function
OFFSET(C$2,3*(ROW()-ROW(D$8)),0,6,1) will give you the range C2:C7, whereas
in cell D9 the same function will give a range moved down three rows
(because of the fact that the current row number referred to by ROW() has
increased from 8 to 9), and now refers to the range C5:C10.
Q.E.D.
--
David Biddulph

"Speedy" wrote in message
...
Can you explain what your formula means. My data starts in C2 and in D8 I
have =average(c2:c7). In D9 is =average(c5:c10). In D10 =average(c8:c13)
and
so on. I want to average.

Speedy

"David Biddulph" wrote:

If your data values start in row 2 (with headers in row 1), then in D4
use
the formula =AVERAGE(OFFSET(C$2,3*(ROW()-ROW(D$4)),0,6,1)) and copy down.
You can modify the formula to cope with a different length of period and
with looking forward not back.
--
David Biddulph

"Speedy" wrote in message
...
The numeric values in colC are the ones to deal with i.e. take their
average.
colA only contains what I have typed out i.e a date and the 3-month.
The
numeric values in colC correspond to the month values as defined by
colB.
My
aim is to find the mean value of the predictor variable (i.e colC)
during
the
past 6 months. And this mean value is to go into colD. I have many
predictor
variables and colC is only one of them. My problem is a forecasting
problem.

There is a 2nd question to my problem which instead of looking
backwards
it
is looking ahead and taking the sum of the comming 3 month but probably
it
will be too confusing to do it at once.

However after saying all that I acknowledge your help and am most
thankful.

Speedy


"David Biddulph" wrote:

If the values in A are the means for those periods, then in D3 use the
formula =(A1+A2)/2, and fill down
If the values in A are the totals for those periods, then in D3 use
the
formula =(A1+A2)/6, and fill down
--
David Biddulph

"Speedy" wrote in message
...
I have 3 cols such that they look like this:

colA colB colC colD
1931jfm 1931jan 1 mean of 1930(j,a,s,o,n,d)
1931amj 1931feb 2 mean of 1930(o,n,d),1931(j,f,m)
1931jas 1931mar 3 ....
1931ond 1931apr 4 .....

where j is jan, f is feb, m is march and so on
1930(j,a,s,o,n,d) is jul1930, aug1930,sep1930 and so on till
dec1930
I want to calculate the mean of the previous 6 months which is to
become
colD.

Speedy