Automatically make multiple averages in same column?

[Corey872]

Greetings,

I have a column of numbers, basically groups of numbers with one blank
row between each group (the actual size is 3000+ lines). Example as
follws:

1
2
3
(2)
4
5
6
7
8
(6)
7
8
9
10
(8.5)

....

Is there any way to generate the average of each group without going
through and using the "avg" function by hand. In short, some formula
that would recognize the blank, and average all the numbers to the
next blank, spit that number out and calculate a new average for the
next group of numbers?

Any help would be greatly appreciated!

Corey

 

[Jason Morin]

Try:

=AVERAGE(OFFSET(INDIRECT("A"&SMALL(IF(A1:A22="",ROW
(A1:A22)),C1)),,,SMALL(IF(A1:A22="",ROW(A1:A22)),C1+1)-
SMALL(IF(A1:A22="",ROW(A1:A22)),C1)))

Array-entered (ctrl/shift/enter). In this example, I left
A1 and A23 blank. C1 represents the group number (group 1
being the first group at the top). Instead of changing the
number in C1, you could list 1 through N across the row,
and then drag the formula across as well (absolute the
ranges in the formula so they don't change).

If you want a the file I tested this with, send me an e-
mail directly with a copy of your post.

HTH
Jason
Atlanta, GA

 

[Corey872]

Thanks for the suggestion. That seems to work pretty well.
I appreciate your time!

Corey