Waste calculations

[Joshcurrie]

Hi,
I need to calculate waste quantities in a spreadsheet for individual jobs at my workplace.
For example, I might have 50 units of four different colours of yarn (200 total,50 red, 50 black, 50 white, 50 beige), if each colour has a different average weight, eg colour 1 weighs 1000g per unit, colour 2 weighs 1100g , colour 3 weighs 1050g, colour 4 weighs 1150g.
I consume a set amount of each unit, but need to work out how much I have consumed using;
1) The number of units of each colour which is disposed of (some units might not be disposed of).
2) the total weight of everything which is disposed of.

The amount consumed of each unit is always equal, if I consumed 200g, and had 200 units, I would use 1g/unit.

I don't have a way to measure the weight of what was consumed of each colour, this must be calculated in the spreadsheet.

Eg.
Colour 1 - 10 units - 1kg/unit - 10 scrapped
Colour 2 - 20 units - 1.2kg/unit - 20 scrapped
Colour 3 - 20 units - 1.1kg/unit - 0 scrapped

Total weight of all scrap = 20kg

What weight was scrapped of each colour (obviously 0 for colour 3)?

What weight was consumed of each colour? Including the amount of colour 3 which was consumed (but not in full)?

Im sure there is a solution to this problem, please could somebody help me?

 

[Ezguy4u]

Ok let's see if we can get the ball rolling. This should generate more questions then answers. Let me start with Colour 2 20 units - 1.2Kg/unit - 20 scrapped. How did you come up with 20 scrapped.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Units	Color	Average Weight per Unit in Gram	Average Weight per Unit in KGram	Total Gram	Kilo Grams		Eg. Units	Total Average Grams Used	Uints Left	Total Average Grams Left
2	50	Colour 1	1,000	1.00	50,000	50.0		10	10,000	40	40,000
3	50	Colour 2	1,100	1.10	55,000	55.0		20	22,000	30	33,000
4	50	Colour 3	1,050	1.05	52,500	52.5		20	21,000	30	31,500
5	50	Colour 4	1,150	1.15	57,500	57.5
Yarn

Cell Formulas
Range	Formula
D2:D5,F2:F5	D2	=C2/1000
E2:E5	E2	=A2*C2
I2:I4	I2	=H2*C2
J2:J4	J2	=A2-H2
K2:K4	K2	=J2*C2