average formula?

  • Thread starter Thread starter excel0003
  • Start date Start date
E

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)
 
In cell B5, enter the formula =AVERAGE(a1:a5)

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

James
 
davesexcel said:
is this in one cell??

=AVERAGE(A1:A5)&" "&AVERAGE(A6:A10)&" "&AVERAGE(A11:A15)
Click to expand...


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
 
Enter this anywhere, and copy down as needed:

=AVERAGE(INDEX(A:A,5*ROWS($1:1)-4):INDEX(A:A,5*ROWS($1:1)))
 
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
 
Back
Top