I am trying to determine a quantity per assembly useage for approximately
1000 lower level components as a weighted average, based on a forecasted
demand for the parent item. However, the data was extracted from our MRP
system and given to me as pictured below. I will need to associate the demand
of the parent item, ie if 87544378 has a demand of 100pc for 12mos. I would
like to calculate useage as a weighted average against the actual demand and
have over 1000 component items to calculate. Would this be easier done as a
macro?

component parent Quantity per Assembly
9706702 87544378 11
9706702 87544379 11
9706702 87558317 9
9706717 87558318 9
214600 86636474 2
214600 86636475 2
214600 86636483 1
214600 87016080 1
86979102 86636474 2
86979102 86636475 2
86979102 86636476 2
86979102 86636483 1

If you want the sum of 'quantity per assembly' for parent 87544378
use this
=SUMPRODUCT(--(B1:B100=87544378),(C1:C100))
assuming 87544378 is a number... if text put quotes around it like this
"87544378"

to get the count use
=SUMPRODUCT(--(B1:B100=87544378))

If cell D1 has 87544378 then you can use
=SUMPRODUCT(--(B1:B100=D1))
With these two you can build the weighted average as per your needs...

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"CandiC" wrote:

I am trying to determine a quantity per assembly useage for approximately
1000 lower level components as a weighted average, based on a forecasted
demand for the parent item. However, the data was extracted from our MRP
system and given to me as pictured below. I will need to associate the demand
of the parent item, ie if 87544378 has a demand of 100pc for 12mos. I would
like to calculate useage as a weighted average against the actual demand and
have over 1000 component items to calculate. Would this be easier done as a
macro?

component parent Quantity per Assembly
9706702 87544378 11
9706702 87544379 11
9706702 87558317 9
9706717 87558318 9
214600 86636474 2
214600 86636475 2
214600 86636483 1
214600 87016080 1
86979102 86636474 2
86979102 86636475 2
86979102 86636476 2
86979102 86636483 1

Thank you so much for your help.

"Sheeloo" wrote:

If you want the sum of 'quantity per assembly' for parent 87544378
use this
=SUMPRODUCT(--(B1:B100=87544378),(C1:C100))
assuming 87544378 is a number... if text put quotes around it like this
"87544378"

to get the count use
=SUMPRODUCT(--(B1:B100=87544378))

If cell D1 has 87544378 then you can use
=SUMPRODUCT(--(B1:B100=D1))
With these two you can build the weighted average as per your needs...

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"CandiC" wrote:

I am trying to determine a quantity per assembly useage for approximately
1000 lower level components as a weighted average, based on a forecasted
demand for the parent item. However, the data was extracted from our MRP
system and given to me as pictured below. I will need to associate the demand
of the parent item, ie if 87544378 has a demand of 100pc for 12mos. I would
like to calculate useage as a weighted average against the actual demand and
have over 1000 component items to calculate. Would this be easier done as a
macro?

component parent Quantity per Assembly
9706702 87544378 11
9706702 87544379 11
9706702 87558317 9
9706717 87558318 9
214600 86636474 2
214600 86636475 2
214600 86636483 1
214600 87016080 1
86979102 86636474 2
86979102 86636475 2
86979102 86636476 2
86979102 86636483 1