Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
compressing long columns into subset averages
Hello,
I am looking for an easy way to compress a large number of data, given in one long column, into a shorter column that contains the average of every N cells. Say for instance I have a column with one thousand numbers, and I want a new column of the one hundred 10-item averages. The averages would be consecutive, so if A1:A1000 contains my data, B1 would contain average(A1:A10), B2 would have average(A11:A20), and so forth. I would want N (here 10) to be user-specifiable (is that a word?), or it could be taken from a cell. I can think of a worksheet to do this, but is there a quick way to do it in the same worksheet that contains my data, e.g. using a function? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
compressing long columns into subset averages
Margo,
In cell B1, enter =IF(COUNTA(A:A)($C$1*(ROWS($A$1:A1)-1)),SUM(OFFSET($A$1,$C$1*(ROWS($A$1:A1)-1),0,$C$1)),"") and enter the value of N into C1. Then copy down to match your data in A... HTH, Bernie MS Excel MVP "Margo Guda" wrote in message ... Hello, I am looking for an easy way to compress a large number of data, given in one long column, into a shorter column that contains the average of every N cells. Say for instance I have a column with one thousand numbers, and I want a new column of the one hundred 10-item averages. The averages would be consecutive, so if A1:A1000 contains my data, B1 would contain average(A1:A10), B2 would have average(A11:A20), and so forth. I would want N (here 10) to be user-specifiable (is that a word?), or it could be taken from a cell. I can think of a worksheet to do this, but is there a quick way to do it in the same worksheet that contains my data, e.g. using a function? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
compressing long columns into subset averages
Enter this formula in B1 and copy down as needed:
C1 = 10 =AVERAGE(OFFSET(A$1,(ROWS(B$1:B1)-1)*C$1,,10)) -- Biff Microsoft Excel MVP "Margo Guda" wrote in message ... Hello, I am looking for an easy way to compress a large number of data, given in one long column, into a shorter column that contains the average of every N cells. Say for instance I have a column with one thousand numbers, and I want a new column of the one hundred 10-item averages. The averages would be consecutive, so if A1:A1000 contains my data, B1 would contain average(A1:A10), B2 would have average(A11:A20), and so forth. I would want N (here 10) to be user-specifiable (is that a word?), or it could be taken from a cell. I can think of a worksheet to do this, but is there a quick way to do it in the same worksheet that contains my data, e.g. using a function? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
compressing long columns into subset averages
Typo:
C1 = 10 =AVERAGE(OFFSET(A$1,(ROWS(B$1:B1)-1)*C$1,,10)) Should be: =AVERAGE(OFFSET(A$1,(ROWS(B$1:B1)-1)*C$1,,C$1)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Enter this formula in B1 and copy down as needed: C1 = 10 =AVERAGE(OFFSET(A$1,(ROWS(B$1:B1)-1)*C$1,,10)) -- Biff Microsoft Excel MVP "Margo Guda" wrote in message ... Hello, I am looking for an easy way to compress a large number of data, given in one long column, into a shorter column that contains the average of every N cells. Say for instance I have a column with one thousand numbers, and I want a new column of the one hundred 10-item averages. The averages would be consecutive, so if A1:A1000 contains my data, B1 would contain average(A1:A10), B2 would have average(A11:A20), and so forth. I would want N (here 10) to be user-specifiable (is that a word?), or it could be taken from a cell. I can think of a worksheet to do this, but is there a quick way to do it in the same worksheet that contains my data, e.g. using a function? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
compressing long columns into subset averages
I tried this:
=AVERAGE(INDIRECT("D"&(8+$AJ$7*$AJ9)&":D"&(8+$AJ$7 *$AJ9+$AJ$7-1))) where D8 is the first of the long column of data, and ak8 through a large number contains the compressed averages, while aj8 and up count the instances, starting with 0 in aj8. AJ7 contains the divider. Your method is much more elegant. Thanks! T. Valko wrote: Typo: C1 = 10 =AVERAGE(OFFSET(A$1,(ROWS(B$1:B1)-1)*C$1,,10)) Should be: =AVERAGE(OFFSET(A$1,(ROWS(B$1:B1)-1)*C$1,,C$1)) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
compressing long columns into subset averages
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Margo Guda" wrote in message ... I tried this: =AVERAGE(INDIRECT("D"&(8+$AJ$7*$AJ9)&":D"&(8+$AJ$ 7*$AJ9+$AJ$7-1))) where D8 is the first of the long column of data, and ak8 through a large number contains the compressed averages, while aj8 and up count the instances, starting with 0 in aj8. AJ7 contains the divider. Your method is much more elegant. Thanks! T. Valko wrote: Typo: C1 = 10 =AVERAGE(OFFSET(A$1,(ROWS(B$1:B1)-1)*C$1,,10)) Should be: =AVERAGE(OFFSET(A$1,(ROWS(B$1:B1)-1)*C$1,,C$1)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
How to enter symbols for subset or element of a subset in Excel? | Excel Worksheet Functions | |||
Averages from 2 columns | Excel Worksheet Functions | |||
Possible to apply Autofilter to a subset of available columns? | Excel Discussion (Misc queries) |