compressing long columns into subset averages

[Margo Guda]

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?

 

[Bernie Deitrick]

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

 

[T. Valko]

Enter this formula in B1 and copy down as needed:

C1 = 10

=AVERAGE(OFFSET(A$1,(ROWS(B$1:B1)-1)*C$1,,10))

 

[T. Valko]

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

 

[Margo Guda]

I tried this:
=AVERAGE(INDIRECT("D"&(8+$AJ$7*$AJ9)&""&(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]

You're welcome. Thanks for the feedback!