I have colB containing monthly numeric values. And I want to create colC such
that it calculates the mean of previous 6 values of colB. So in the first row
of colC its obvious that it is zero, 2nd row 2, 3rd row 5, etc. The fact that
column B is so long. and I want an XL function that may be I can copy to all
the cells in colC to do the job for me or you're the expert. many thanks.

colA colB colC
1931jan 2 mean previous 6 values in colB
feb 3
mar 4
.... ..
1976dec 5

Speedy

Hello speedy

Try this formula in C2

=AVERAGE(B$1:B1)

and copy to C6, then in C7

=AVERAGE(B1:B6)

copied down as far as you need

"Speedy" wrote:

I have colB containing monthly numeric values. And I want to create colC such
that it calculates the mean of previous 6 values of colB. So in the first row
of colC its obvious that it is zero, 2nd row 2, 3rd row 5, etc. The fact that
column B is so long. and I want an XL function that may be I can copy to all
the cells in colC to do the job for me or you're the expert. many thanks.

colA colB colC
1931jan 2 mean previous 6 values in colB
feb 3
mar 4
.... ..
1976dec 5

Speedy

Many thanks daddylonglegs for your great help.

Speedy

"daddylonglegs" wrote:

Hello speedy

Try this formula in C2

=AVERAGE(B$1:B1)

and copy to C6, then in C7

=AVERAGE(B1:B6)

copied down as far as you need

"Speedy" wrote:

I have colB containing monthly numeric values. And I want to create colC such
that it calculates the mean of previous 6 values of colB. So in the first row
of colC its obvious that it is zero, 2nd row 2, 3rd row 5, etc. The fact that
column B is so long. and I want an XL function that may be I can copy to all
the cells in colC to do the job for me or you're the expert. many thanks.

colA colB colC
1931jan 2 mean previous 6 values in colB
feb 3
mar 4
.... ..
1976dec 5

Speedy

What if I have the setup below of four cols:

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. The previous 6 months for cell D1 is 1930(j,a,s,o,n,d), cell D2
1930(o,n,d)1931(j,f,m), cell D3 1930(j,f,m,a,m,j) and so on. Because there
are so many columns and they are so long I thought I would seek help to make
my task easier.

Thanks alot

Speedy


"daddylonglegs" wrote:

Hello speedy

Try this formula in C2

=AVERAGE(B$1:B1)

and copy to C6, then in C7

=AVERAGE(B1:B6)

copied down as far as you need

"Speedy" wrote:

I have colB containing monthly numeric values. And I want to create colC such
that it calculates the mean of previous 6 values of colB. So in the first row
of colC its obvious that it is zero, 2nd row 2, 3rd row 5, etc. The fact that
column B is so long. and I want an XL function that may be I can copy to all
the cells in colC to do the job for me or you're the expert. many thanks.

colA colB colC
1931jan 2 mean previous 6 values in colB
feb 3
mar 4
.... ..
1976dec 5

Speedy

I keep messing things up D3 should be 1931(j,f,m,a,m,j) not 1930(j,f,m,a,m,j)
as I have below. Sorry about this, thanks,

Speedy

"Speedy" wrote:

What if I have the setup below of four cols:

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. The previous 6 months for cell D1 is 1930(j,a,s,o,n,d), cell D2
1930(o,n,d)1931(j,f,m), cell D3 1930(j,f,m,a,m,j) and so on. Because there
are so many columns and they are so long I thought I would seek help to make
my task easier.

Thanks alot

Speedy


"daddylonglegs" wrote:

Hello speedy

Try this formula in C2

=AVERAGE(B$1:B1)

and copy to C6, then in C7

=AVERAGE(B1:B6)

copied down as far as you need

"Speedy" wrote:

I have colB containing monthly numeric values. And I want to create colC such
that it calculates the mean of previous 6 values of colB. So in the first row
of colC its obvious that it is zero, 2nd row 2, 3rd row 5, etc. The fact that
column B is so long. and I want an XL function that may be I can copy to all
the cells in colC to do the job for me or you're the expert. many thanks.

colA colB colC
1931jan 2 mean previous 6 values in colB
feb 3
mar 4
.... ..
1976dec 5

Speedy