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?

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?

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?

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?

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))

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))