Formula to allocate costs

[Brusky]

I am trying to estimate average shipping costs per unit. We have been provided a total shipping cost of $300 USD for all 25 items, but not per unit.

Product Name	Qty	Weight (lbs)	Item Cost ($)
Product A	18	40	2,000
Product B	1	35	1,500
Product C	4	25	1,000
Product D	1	3	100
Product E	1	3	100

The average shipping charge is $12. Which looks more reasonable for the higher cost items, but excessive for the lower cost items. What formula would distribute this $300 cost so that the higher weight, higher cost items estimate a higher shipping charge per unit. And the lower weight, lower cost items estimate a lower cost per unit?

 

[awoohaw]

how about weighted averages based on weight or cost:

Book1
	A	B	C	D	E	F
1	Product Name	Qty	Weight (lbs)	Item Cost ($)	on weight	on prices
2	Product A	18	40	2,000	113.21	127.66
3	Product B	1	35	1,500	99.06	95.74
4	Product C	4	25	1,000	70.75	63.83
5	Product D	1	3	100	8.49	6.38
6	Product E	1	3	100	8.49	6.38
7			total cost:	300	300	299.99
Sheet5

Cell Formulas
Range	Formula
E2:E6	E2	=ROUND(C2/SUM($C$2:$C$6)*300,2)
F2:F6	F2	=ROUND(D2/SUM($D$2:$D$6)*300,2)
E7:F7	E7	=SUM(E2:E6)

 

[Brusky]

how about weighted averages based on weight or cost:

Book1
	A	B	C	D	E	F
1	Product Name	Qty	Weight (lbs)	Item Cost ($)	on weight	on prices
2	Product A	18	40	2,000	113.21	127.66
3	Product B	1	35	1,500	99.06	95.74
4	Product C	4	25	1,000	70.75	63.83
5	Product D	1	3	100	8.49	6.38
6	Product E	1	3	100	8.49	6.38
7			total cost:	300	300	299.99
Sheet5

Cell Formulas
Range	Formula
E2:E6	E2	=ROUND(C2/SUM($C$2:$C$6)*300,2)
F2:F6	F2	=ROUND(D2/SUM($D$2:$D$6)*300,2)
E7:F7	E7	=SUM(E2:E6)

this is perfect thank you!