average formula?

[excel0003]

I like to average data by 5 rows.

example,
Column:
A1:2
A2:3
A3:4
A4:5
A5:3
A6:8
A7:9
A8:4
A9:2
A10:5
A11:6
..
AN:
I want to know the formula that calculate Averager of A1 to A5, next
from A6 to A10, next A11 to a15.......automatically. moving average
(increment by 5)

 

[davesexcel]

is this in one cell??

=AVERAGE(A1:A5)&" "&AVERAGE(A6:A10)&" "&AVERAGE(A11:A15)

 

[Guest]

In cell B5, enter the formula =AVERAGE(a1:a5)

Then copy this formula to cell B10, B15, etc.

James

 

[excel0003]

is this in one cell??

=AVERAGE(A1:A5)&" "&AVERAGE(A6:A10)&" "&AVERAGE(A11:A15)

I have 1 column but 1000 rows.
i want to get,
1st cell =average a1 to a5
2nd cell=average of a6 to a10

continue to
last cell= average A(1000-5) to a1000.


will appreciat

 

[RagDyer]

Enter this anywhere, and copy down as needed:

=AVERAGE(INDEX(A:A,5*ROWS($1:1)-4):INDEX(A:A,5*ROWS($1:1)))

 

[excel0003]

i want to copy paste to b1, b2, b3 , not b5, b10,.. i will appreciate

 

[excel0003]

RD,

it works
thank you very much.


pk

Enter this anywhere, and copy down as needed:

=AVERAGE(INDEX(A:A,5*ROWS($1:1)-4):INDEX(A:A,5*ROWS($1:1)))

 

[Guest]

I think the jist is that you want consecutive rows with averages of
non-overlapping 5-row ranges; a total of 200 rows of averages to summarize
1000 rows of data.

James' approach certainly will work, but if you want want to avoid a lot of
copying and pasting try the following formula:

=AVERAGE(OFFSET(A$1,(ROW(C1)-ROW(C$1))*5,0,5,1))

This is assuming the data is from A1:A1000 and the averages are listed from
C1 down to C200

Hope that helps,

TK

 

[RagDyer]

You're welcome, and appreciate the feed-back.