Home |
Search |
Today's Posts |
#1
|
|||
|
|||
averaging multiple items from one list
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
averaging multiple items from one list
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
averaging multiple items from one list
"lespactdeslo" 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):INDE X(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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding multiple items from a list | Excel Worksheet Functions | |||
Pick multiple items from list box | Excel Programming | |||
Deleting multiple items from a list box | Excel Programming | |||
Want Vlookup to list multiple items with the same key? | Excel Worksheet Functions | |||
Multiple comparison of list items. | Excel Programming |