Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average every 20 rows
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average every 20 rows
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? "Karen K" wrote: 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average every 20 rows
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 .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Karen K" <Karen wrote in message ... 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average every 20 rows
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 "Max" wrote in message ... 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 .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Karen K" <Karen wrote in message ... 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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average every 20 rows
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. -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=508032 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to swap rows and columns? | Excel Discussion (Misc queries) | |||
How do I sort (or group?) a spreadsheet by similar rows? | Excel Worksheet Functions | |||
AVERAGE and STDEV functions with logic | Excel Worksheet Functions | |||
Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) | |||
flexible paste rows function that inserts the right number of rows | Excel Discussion (Misc queries) |