Waste calculations

Joshcurrie

New Member
Joined
Oct 19, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Ezguy4u

Board Regular
Joined
Feb 10, 2010
Messages
148
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGHIJK
1UnitsColorAverage Weight per Unit in GramAverage Weight per Unit in KGramTotal GramKilo GramsEg. UnitsTotal Average Grams UsedUints LeftTotal Average Grams Left
250Colour 1 1,000 1.00 50,000 50.0 10 10,000 40 40,000
350Colour 2 1,100 1.10 55,000 55.0 20 22,000 30 33,000
450Colour 3 1,050 1.05 52,500 52.5 20 21,000 30 31,500
550Colour 4 1,150 1.15 57,500 57.5
Yarn
Cell Formulas
RangeFormula
D2:D5,F2:F5D2=C2/1000
E2:E5E2=A2*C2
I2:I4I2=H2*C2
J2:J4J2=A2-H2
K2:K4K2=J2*C2
 

Watch MrExcel Video

Forum statistics

Threads
1,127,210
Messages
5,623,407
Members
415,972
Latest member
SY1234

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top