averaging multiple items from one list

[lespactdeslo]

Hey all,

I'm having issues with averaging numbers from a list. I have a list o
approximately 2000 values that need to be averaged in clumps of 6. Whe
I create an average formula in a second column with the the first fe
equations being as such: =Average(W9:W14); =Average(W15:W20)
=Average(W21:W26), then highlight the three boxes and drag the righ
corner down a few rows, I get repeating groupings of three boxes tha
average 6 numbers each but are not consecutive.

Here's what I'm returning:
=Average(W9:W14); =Average(W15:W20); =Average(W21:W26)
=Average(W12:W17); =Average(W18:W23); =Average(W24:29)
=Average(W15:W20); =Average(W21:W26); =Average(W27:W32)
=Average(W18:W23); =Average(W24:W29); =Average(W30:W35)

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

Any help would be wonderful

 

[GS]

lespactdeslo wrote on 5/25/2012 :

Hey all,

I'm having issues with averaging numbers from a list. 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 column with the the first few
equations being as such: =Average(W9:W14); =Average(W15:W20);
=Average(W21:W26), then highlight the three boxes and drag the right
corner down a few rows, I get repeating groupings of three boxes that
average 6 numbers each but are not consecutive.

Here's what I'm returning:
=Average(W9:W14); =Average(W15:W20); =Average(W21:W26)
=Average(W12:W17); =Average(W18:W23); =Average(W24:29)
=Average(W15:W20); =Average(W21:W26); =Average(W27:W32)
=Average(W18:W23); =Average(W24:W29); =Average(W30:W35)

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

Any help would be wonderful.

Excel is unhelpfully following the pattern of the 3 selected cells,
thinking you want to copy that pattern down to where you drag. So the
3rd cell down from 9 is 12; 3rd down from 15 is 18; 3rd down from 21 is
24; ..and so on!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[joeu2004]

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.