L
Legacy 170226
Guest
Hi all -
Excel 2010, windows 7
I have a question that I think will involve SumIf, AverageIf and/or SumProduct, but I'm not quite sure.
I have series of import data noting different products being traded
Excel 2010, windows 7
I have a question that I think will involve SumIf, AverageIf and/or SumProduct, but I'm not quite sure.
I have series of import data noting different products being traded
Code:
, the source of the data [SOURCE], and the time period (here just noted by month). Sample data below.
For the most part data is all in the same format: Months all note tons; Source all note the name of the source.
Code is problematic, because the data are in different format within the field. These data are HS-codes - commodities are divided into two-digit "chapters", and including more digits beyond that provides increased specificity on the product in question (see http://www.usitc.gov/publications/docs/tata/hts/bychapter/1300C15.pdf for further detail on these codes). For the most part they are 6-digit figures. Some are 4 digit and some are 5 digit.
Based on a code referenced in another cell, I would like to do the following operations in the following order[INDENT]
1) summarize all entries by source that have codes that share the same initial four figures from left to right* (e.g. where the text in the cell begins with "1507")
[/INDENT]
[INDENT]2) Average those sums (for each 4-digit CODE) across all sources for a total average for the period
*note the CODE value being searched for in the equation would be referenced from another cell
[/INDENT]
so for cell C30, which looks to perform the operation for all entries where the 4 left-most figures in CODE begin with "1507", the result would equal =Average((C2+C3),(C8+C9),(C19),(C27),(C28), but it would do so without doing hard references to those cells. In the example table below, I would like for the formula in C30 to reference B30 to find the code that it is looking for.
A few things of note:
[LIST]
[*]I would like to do this operation without adding another column (=left(Bx,4)) to pull the first four digits from the entry in column B, which I know would make for a simpler formula.
[*]Sometimes the values in CODE begin with a zero, and must be preceded with an apostrophe to preserve the initial zero (e.g.: '071022). thus entries in CODE are sometimes recorded as text and not as numbers. i would like for my formula to take this into account.
[*]Ideally, I'd like to know how to do this using an array (CSE) forumula, as well as one that does not do it use CSE. But I'd be happy with either =).
[/LIST]
thanks in advance for any assistance - n
[TABLE="width: 512"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]A[/TD]
[TD="class: xl100, width: 64"]B[/TD]
[TD="class: xl101, width: 64"]C[/TD]
[TD="class: xl101, width: 64"]D[/TD]
[TD="class: xl101, width: 64"]E[/TD]
[TD="class: xl101, width: 64"]F[/TD]
[TD="class: xl101, width: 64"]G[/TD]
[TD="class: xl101, width: 64"]H[/TD]
[/TR]
[TR]
[TD="width: 64"]1[/TD]
[TD="width: 64"]Source[/TD]
[TD="class: xl100, width: 64"]Code[/TD]
[TD="class: xl101, width: 64"]Jan[/TD]
[TD="class: xl101, width: 64"]Feb[/TD]
[TD="class: xl101, width: 64"]Mar[/TD]
[TD="class: xl101, width: 64"]April[/TD]
[TD="class: xl101, width: 64"]May[/TD]
[TD="class: xl101, width: 64"]June[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Tom[/TD]
[TD="class: xl100"]150710[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]8[/TD]
[TD="class: xl103, align: right"]95[/TD]
[TD="class: xl103, align: right"]922[/TD]
[TD="class: xl103, align: right"]1174[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Tom[/TD]
[TD="class: xl100"]150790[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]25[/TD]
[TD="class: xl103, align: right"]28[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Tom[/TD]
[TD="class: xl100"]150810[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]22[/TD]
[TD="class: xl103, align: right"]33[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Tom[/TD]
[TD="class: xl100"]150890[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]1[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Tom[/TD]
[TD="class: xl100"]151110[/TD]
[TD="class: xl103, align: right"]10278[/TD]
[TD="class: xl103, align: right"]3436[/TD]
[TD="class: xl103, align: right"]8317[/TD]
[TD="class: xl103, align: right"]15901[/TD]
[TD="class: xl103, align: right"]1066[/TD]
[TD="class: xl103, align: right"]192[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Tom[/TD]
[TD="class: xl100"]151190[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]2910[/TD]
[TD="class: xl103, align: right"]4506[/TD]
[TD="class: xl103, align: right"]17339[/TD]
[TD="class: xl103, align: right"]3069[/TD]
[TD="class: xl103, align: right"]42494[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Mike[/TD]
[TD="class: xl100"]150710[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]96[/TD]
[TD="class: xl103, align: right"]922[/TD]
[TD="class: xl103, align: right"]1174[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Mike[/TD]
[TD="class: xl100"]150790[/TD]
[TD="class: xl103, align: right"]1385[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]26[/TD]
[TD="class: xl103, align: right"]29[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Mike[/TD]
[TD="class: xl100"]150810[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]22[/TD]
[TD="class: xl103, align: right"]16[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Mike[/TD]
[TD="class: xl100"]150890[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Mike[/TD]
[TD="class: xl100"]151110[/TD]
[TD="class: xl103, align: right"]10278[/TD]
[TD="class: xl103, align: right"]3437[/TD]
[TD="class: xl103, align: right"]3766[/TD]
[TD="class: xl103, align: right"]15901[/TD]
[TD="class: xl103, align: right"]1066[/TD]
[TD="class: xl103, align: right"]15459[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Mike[/TD]
[TD="class: xl100"]151190[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]2909[/TD]
[TD="class: xl103, align: right"]4506[/TD]
[TD="class: xl103, align: right"]17338[/TD]
[TD="class: xl103, align: right"]3069[/TD]
[TD="class: xl103, align: right"]42611[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Mike[/TD]
[TD="class: xl100"]151590[/TD]
[TD="class: xl103, align: right"]707[/TD]
[TD="class: xl103, align: right"]3[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Shirley[/TD]
[TD="class: xl100"]151229[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Shirley[/TD]
[TD="class: xl100"]150890[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Shirley[/TD]
[TD="class: xl100"]151519[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Shirley[/TD]
[TD="class: xl100"]151590[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Shirley[/TD]
[TD="class: xl100"]150790[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Jane[/TD]
[TD="class: xl102, align: right"]1508[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Jane[/TD]
[TD="class: xl102, align: right"]1511[/TD]
[TD="class: xl103, align: right"]3200[/TD]
[TD="class: xl103, align: right"]3200[/TD]
[TD="class: xl103, align: right"]3200[/TD]
[TD="class: xl103, align: right"]3200[/TD]
[TD="class: xl103, align: right"]3200[/TD]
[TD="class: xl103, align: right"]3200[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]Jane[/TD]
[TD="class: xl102, align: right"]151211[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]Jane[/TD]
[TD="class: xl102, align: right"]151212[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]Jane[/TD]
[TD="class: xl102, align: right"]1514[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]Jane[/TD]
[TD="class: xl102, align: right"]15131[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]Jane[/TD]
[TD="class: xl102, align: right"]15132[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]Jane[/TD]
[TD="class: xl102, align: right"]1507[/TD]
[TD="class: xl103, align: right"]32[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]Scott[/TD]
[TD="class: xl102, align: right"]15079[/TD]
[TD="class: xl103, align: right"]890[/TD]
[TD="class: xl103, align: right"]65[/TD]
[TD="class: xl103, align: right"]456[/TD]
[TD="class: xl103, align: right"]500[/TD]
[TD="class: xl103, align: right"]41[/TD]
[TD="class: xl103, align: right"]0[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD][/TD]
[TD="class: xl102, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]All sources[/TD]
[TD="class: xl102, align: right"]1507[/TD]
[TD="class: xl103, align: right"]X?
(see formula above table)[/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]All sources[/TD]
[TD="class: xl102, align: right"]1508[/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]All sources[/TD]
[TD="class: xl102, align: right"]1509[/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]All sources[/TD]
[TD="class: xl102, align: right"]1510[/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]All sources[/TD]
[TD="class: xl102, align: right"]1511[/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]All sources[/TD]
[TD="class: xl102, align: right"]1512[/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]All sources[/TD]
[TD="class: xl102, align: right"]1513[/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD]All sources[/TD]
[TD="class: xl102, align: right"]1514[/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]All sources[/TD]
[TD="class: xl102, align: right"]1515[/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[TD="class: xl103, align: right"][/TD]
[/TR]
</tbody>[/TABLE]