Moving average in a column

[liiamra]

Hi All!

This is my second question in this great forum - thanks to CircledChicken, my first question was perfectly answered.

This question should be easier I guess and it is again about referencing.

I have the following table:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]=average(B1:B3)[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]=average(C2:C4)[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]=average(D3:D5)[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]=average(E4:E6)[/TD]
[TD][/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]and so on[/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]


The thing is that I need the formula that can be used in A, so that when I copy it downward, it takes the average of the next column- but I need to select only relevant cells like it is in the above table and not the whole column as there are other data in the same sheet.

Any help is appreciated.

 

[tinguyen]

Hi liiamra,

Try this formula in cell A1 :

=AVERAGE(OFFSET(B1,,ROW()-1,3))

 

[liiamra]

Hello Tinguyen,
Thanks too much for your assistance!

It worked! but I don't understand the logic behind it- well I do understand the row()-1,3 thing, but how does it move in columns?

Is't the cell reference of B? for example B4 means the 4th column?

Thanks again and all best//
Ali

 

[tinguyen]

The row()-1,3 thing does the move in columns !

I'll try to explain.

In A1, you will have :

=AVERAGE(OFFSET(B1,,1-1,3))

=AVERAGE(OFFSET(B1,,0,3))

as the row of the cell A1 is 1. For the first cell, there will be a move of 0 column.

In A2, you will have :

=AVERAGE(OFFSET(B1,,2-1,3))

=AVERAGE(OFFSET(B1,,1,3))

as the row of the cell A2 is 2. The cells will therefore move 1 column to the right !

And so on.

Tin

 

[liiamra]

lollll :D

Well now the problem is not only solved but explained too

Thanks alot Tin - enjoy the Easter holidays!
All Best//
Ali