software

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!

One way you could do it. Insert a column to the left of your data (lets say
this new column is Column A). Enter quot;Aquot; in the first 20 cells of this
column. Enter quot;Bquot; in the next 20 cells. Now select these cells with quot;Aquot; and
quot;Bquot; 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?

quot;Karen Kquot; wrote:

gt; I have a data of 14000 rows. I need Average on every 20 cells. Instead of
gt; copying and pasting the formula into every 20 cells to get the result which
gt; will take forever is there a function to obtain the results automatically?
gt;
gt; I'm trying group every 20 rows automatically unfortunately it didn't help
gt; either. Help!

Another play to try ..

Assuming data is in A1:A14000

Put in B1:
=AVERAGE(OFFSET(INDIRECT(quot;Aquot;amp;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 ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Karen Kquot; lt;Karen gt; wrote in message
...
gt; I have a data of 14000 rows. I need Average on every 20 cells. Instead
of
gt; copying and pasting the formula into every 20 cells to get the result
which
gt; will take forever is there a function to obtain the results automatically?
gt;
gt; I'm trying group every 20 rows automatically unfortunately it didn't help
gt; either. Help!
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

quot;Maxquot; gt; wrote in message
...
gt; Another play to try ..
gt;
gt; Assuming data is in A1:A14000
gt;
gt; Put in B1:
gt; =AVERAGE(OFFSET(INDIRECT(quot;Aquot;amp;ROW(A1)*20-19),,,20))
gt; Copy B1 down to B700 (14000 / 20 = 700)
gt;
gt; B1 returns the same as: =AVERAGE(A1:A20)
gt; B2 returns the same as: =AVERAGE(A21:A40)
gt; and so on ..
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt; quot;Karen Kquot; lt;Karen gt; wrote in message
gt; ...
gt;gt; I have a data of 14000 rows. I need Average on every 20 cells. Instead
gt; of
gt;gt; copying and pasting the formula into every 20 cells to get the result
gt; which
gt;gt; will take forever is there a function to obtain the results
gt;gt; automatically?
gt;gt;
gt;gt; I'm trying group every 20 rows automatically unfortunately it didn't help
gt;gt; either. Help!
gt;
gt;

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),quot;quot;)

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.--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=508032