Determining weighted itemized costs

[Jim D]

I have list of items of varying counts andjust a total price for the bundle. I am hoping to be able to determine a unit price for each item based on it's weight which I have calcualted by individually dividing the quantity of each into the total quantity. Any suggestions?

Example to solve for.
Item A 100 ea
Item B 150 ea
Item C 500 ea
Total Cost $100,000

 

[Eric W]

I'm afraid you need a bit more than that. In mathematical terms you have three unknowns (cost1, cost2, cost3) and one equation 100*c1 + 150*c2 + 500*c3 = 100,000. There is an infinity of solutions. Here are 3:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K
1	Item	Quantity		Unit Cost	Cost		Unit Cost	Cost		Unit Cost	Cost
2	A	100		133.3333	13333.33		200	20000		175	17500
3	B	150		133.3333	20000		150	22500		210	31500
4	C	500		133.3333	66666.67		115	57500		102	51000
5
6	Total Cost	100,000			100000			100000			100000
Sheet30

Cell Formulas
Range	Formula
D2:D4	D2	=E2/B2
E2:E4	E2	=B2/SUM(B$2:B$4)*B$6
H2:H4,K2:K4	H2	=G2*$B2
G4	G4	=57500/500
J4	J4	=51000/500
E6,K6,H6	E6	=SUM(E2:E4)

All very different, but they sum up the same. To be able to come up with an answer, you'd need some way to add some more conditions or constraints.

 

[Jim D]

I'm afraid you need a bit more than that. In mathematical terms you have three unknowns (cost1, cost2, cost3) and one equation 100*c1 + 150*c2 + 500*c3 = 100,000. There is an infinity of solutions. Here are 3:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K
1	Item	Quantity		Unit Cost	Cost		Unit Cost	Cost		Unit Cost	Cost
2	A	100		133.3333	13333.33		200	20000		175	17500
3	B	150		133.3333	20000		150	22500		210	31500
4	C	500		133.3333	66666.67		115	57500		102	51000
5
6	Total Cost	100,000			100000			100000			100000
Sheet30

Cell Formulas
Range	Formula
D2:D4	D2	=E2/B2
E2:E4	E2	=B2/SUM(B$2:B$4)*B$6
H2:H4,K2:K4	H2	=G2*$B2
G4	G4	=57500/500
J4	J4	=51000/500
E6,K6,H6	E6	=SUM(E2:E4)

All very different, but they sum up the same. To be able to come up with an answer, you'd need some way to add some more conditions or constraints.

Thanks Eric!