"lespactdeslo" <(E-Mail Removed)> wrote:
> I have a list of approximately 2000 values that need to be
> averaged in clumps of 6. When I create an average formula
> in a second
[....]
> here is what I would like to return:
> =Average(W9:W14); =Average(W15:W20); =Average(W21:W26)
> =Average(W27:W32); =Average(W33:W38); =Average(W39:44)
> =Average(W45:W50); =Average(W51:W56); =Average(W57:W62)
> =Average(W63:W68); =Average(W69:W74); =Average(W75:W80)
Enter the following formula into X9 and copy down in X10, X11 etc until you
get a #DIV/0 error, which indicates that there is no more data in column W:
=AVERAGE(INDEX(W:W,9+ROWS($X$9:X9)*6-6):INDEX(W:W,9+ROWS($X$9:X9)*6-1))
Note: I left the formula unsimplified so that you can see how it is
derived. The 9 refers to W9. The 6 refers to the "clump" size. Once you
understand that, obviously the formula can be simplified arithmetically, for
example:
=AVERAGE(INDEX(W:W,3+ROWS($X$9:X9)*6):INDEX(W:W,8+ROWS($X$9:X9)*6))
If you have Excel 2007 or later, you can avoid the #DIV/0 error and allow
for more or less data in column W by putting the following formula into X9
and copying down through row 2000 "approximately":
=IFERROR(AVERAGE(INDEX(W:W,3+ROWS($X$9:X9)*6):INDEX(W:W,8+ROWS($X$9:X9)*6)),"")
PS: I prefer to use the dynamic reference of the form INDEX(...):INDEX(...)
instead of using OFFSET, which is simpler to type, because OFFSET is a
volatile function. Thus, AVERAGE(OFFSET(...)) is recalculated every time
Excel (re)calculates anything in the workbook, notably after any cell in any
worksheet is edited.
|
Similar Threads
