Average every 20 rows

K

Karen K

I have a data of 14000 rows. I need Average on every 20 cells. Instead of
copying and pasting the formula into every 20 cells to get the result which
will take forever is there a function to obtain the results automatically?

I'm trying group every 20 rows automatically unfortunately it didn't help
either. Help!
 
J

JMB

One way you could do it. Insert a column to the left of your data (lets say
this new column is Column A). Enter "A" in the first 20 cells of this
column. Enter "B" in the next 20 cells. Now select these cells with "A" and
"B" and copy. Now select the rest of column A and paste (note that your
paste range must be divisible by 40 - subtract any headers you may have).
Now, you should have alternating groups of 20 A's and B's in column A.

Then select your table (including column A), click Data/Subtotal. At each
change in Column A, use Average on whatever column contains the data you want
averaged. Does this help?
 
M

Max

Another play to try ..

Assuming data is in A1:A14000

Put in B1:
=AVERAGE(OFFSET(INDIRECT("A"&ROW(A1)*20-19),,,20))
Copy B1 down to B700 (14000 / 20 = 700)

B1 returns the same as: =AVERAGE(A1:A20)
B2 returns the same as: =AVERAGE(A21:A40)
and so on ..
 
B

Biff

A non-volatile alternative:

=AVERAGE(INDEX(A$1:A$100,(ROWS($1:1)-1)*20+1):INDEX(A$1:A$100,ROWS($1:1)*20))

Biff
 
D

daddylonglegs

Assuming your data starts at A1 then this formula in B1 filled down
column

=IF(MOD(ROW()-ROW(A$1)+1,20)=1,AVERAGE(A1:A20),"")

will give you an average in B1 of A1:A20, an average in B21 of A21:A40
and so on, alter formula to suit if data starts elsewhere.
 
Top