TO Average Previous values

[Guest]

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

 

[David Biddulph]

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

 

[Guest]

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]

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.

 

[Guest]

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]

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.

 

[Guest]

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]

The syntax of the OFFSET function is shown in Help. It says

"OFFSET(reference,rows,cols,height,width)
Reference is the reference from which you want to base the offset.
Reference must refer to a cell or range of adjacent cells; otherwise, OFFSET
returns the #VALUE! error value.

Rows is the number of rows, up or down, that you want the upper-left cell
to refer to. Using 5 as the rows argument specifies that the upper-left cell
in the reference is five rows below reference. Rows can be positive (which
means below the starting reference) or negative (which means above the
starting reference).

Cols is the number of columns, to the left or right, that you want the
upper-left cell of the result to refer to. Using 5 as the cols argument
specifies that the upper-left cell in the reference is five columns to the
right of reference. Cols can be positive (which means to the right of the
starting reference) or negative (which means to the left of the starting
reference).

Height is the height, in number of rows, that you want the returned
reference to be. Height must be a positive number.

Width is the width, in number of columns, that you want the returned
reference to be. Width must be a positive number."