SUM PRODUCT APPLICATION

[CandiC]

Column D712 is calculated using the formula
=SUMPRODUCT(--($A$6:$A$9637=A7),($C$6:$C$9637)) to get the total amount of
components(A6) demand to build 1 per each assembly(B6) that is it used in .
However, I need to calculate a weighted average of usage ie (divide total
component usage for all assemblies by total number of assemblies) and weigh
that against the actual 12 month sales demand (a2) for the Parent assembly
(B6). I am stuck on how to merge this information together. Please help
create an extension to the formula in Column D or any advise as to how I can
arrange this information better.

Parent item:
87638436 A1
86636476 B1
86636483 C1
87016080 D1

12mo actual demand for parent item:
100 A2
125 B2
150 C2
350 D2

A6 B6 C6
D6
Component Parent(assembly) total per asbly Total comp.Demand
87060761 87638436 1 1
87060762 86636476 3 176
87060762 86636483 23 176
87060762 87016080 4 176
87060762 87016088 7 176
87060762 87517379 10 176
87060762 87517380 15 176
87060762 87544372 22 176
87060762 87544377 22 176
87060762 87602239 22 176
87060762 87602240 24 176
87060762 87638426 24 176
87060784 87638436 8 8
87060824 87055844 15 21
87060824 87360540 6 21
87060825 87055844 44 71
87060825 87360540 21 71
87060825 87382361 6 71
87060833 87382361 1 1

 

[Jim Cone]

Why not calculate the total numbers of components required?
Sort the data by the component column.
Replace the sumproduct formula in D7 with...
=IF(ISNUMBER(MATCH(B7,$A$1:$D$1,FALSE)),HLOOKUP(B7,$A$1:$D$2,2,FALSE)*C7,"")
Fill the formula down.
Use Data | Subtotals to sum the total required for each component.
--
Jim Cone
Portland, Oregon USA





"CandiC" <[email protected]>
wrote in message
Column D712 is calculated using the formula
=SUMPRODUCT(--($A$6:$A$9637=A7),($C$6:$C$9637)) to get the total amount of
components(A6) demand to build 1 per each assembly(B6) that is it used in .
However, I need to calculate a weighted average of usage ie (divide total
component usage for all assemblies by total number of assemblies) and weigh
that against the actual 12 month sales demand (a2) for the Parent assembly
(B6). I am stuck on how to merge this information together. Please help
create an extension to the formula in Column D or any advise as to how I can
arrange this information better.

Parent item:
87638436 A1
86636476 B1
86636483 C1
87016080 D1

12mo actual demand for parent item:
100 A2
125 B2
150 C2
350 D2

A6 B6 C6
D6
Component Parent(assembly) total per asbly Total comp.Demand
87060761 87638436 1 1
87060762 86636476 3 176
87060762 86636483 23 176
87060762 87016080 4 176
87060762 87016088 7 176
87060762 87517379 10 176
87060762 87517380 15 176
87060762 87544372 22 176
87060762 87544377 22 176
87060762 87602239 22 176
87060762 87602240 24 176
87060762 87638426 24 176
87060784 87638436 8 8
87060824 87055844 15 21
87060824 87360540 6 21
87060825 87055844 44 71
87060825 87360540 21 71
87060825 87382361 6 71
87060833 87382361 1 1