Re: averaging multiple items from one list

joeu2004
Guest
Posts: n/a

 25th May 2012
"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.

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post GS Microsoft Excel Worksheet Functions 0 25th May 2012 10:38 PM shane Microsoft Access 5 25th Feb 2009 05:01 PM zwestbrook Microsoft Excel Programming 4 18th Sep 2008 10:32 PM Paul D. Simon Microsoft Excel Programming 1 4th Aug 2005 09:17 PM Frank West Microsoft Excel Misc 2 18th Dec 2003 08:38 AM

Features