Need help with a formula that could help me calculate some numbers that I need to issue...

[webuxer]

Hi all,

I need some help from the experts. This is my issue and I can’t determine how to make it work on excel (if is even possible). I work with long plastic tubes, as you can see in my screenshot on column C is the length in feets (in my example column C = 208. In column E, I keep inventory of how many pcs I have of (208 feets long plastic tubes. In column F, I have a formula that determines how many pcs I can make specifying the size by inches in the formula. Example ( this is the formula I have in column F =FLOOR((C47/5.4),1) As you can see in the formula I inserted 5.4, so based on this a material of 208 feets yields 38pcs of 5.4 inches approx… This is my issue that I want to be able to accomplish with excel. Let’s say that I want 12pcs of 5.4 inches, 12pcs of 6.0 inches and 16pcs of 6.9 inches. Since I only have a 1pc of 208ft I want to figure out how many feets I need to cut the material and issue it to each of the 3 different jobs to make up the pieces, the 12pcs of 5.4, the 12pcs of 6.0 and the 16pcs of 6.9. How can I make formula in excel so that it can calculate this automatically and use the 208ft long tube? Maybe have a formula that throws me the number that I need to issue to each job in column H, I and J. In case that I can’t make the pieces that I need because there’s not enough material, I can modify the quantities either add more pieces or reduce the pieces, this can be done because we want to use the full length of the material as possible (we want the less material to go to waste) Please help

Here's the screenshot;
http://i59.tinypic.com/144490.jpg

Thank you in advance

 

[oldbrewer]

		length of stock pipe	208	feet
				2496	inches
		part lengths required (inches)								parts per 208 feet length
					quan	length								WASTE	WASTE USE	FINAL WASTE
				5.4	12	64.8						5.4	462.22	1.20	0	1.20
				6.0	12	72						6.0	416.00	0.00	0	0.00
				6.9	16	110.4						6.9	361.74	5.10	0	5.10
						247.2	total
												5.4
												6.9
											subtotal	12.3	202.93	11.40	11.4	0.00
												5.4
												6.0
											subtotal	11.4	218.95	10.80	6.9	3.90
		length of stock pipe	208	feet	pieces in stock	19
				2496	inches	stock inches	47424
		part lengths required (inches)								6.0
					quan	length						6.9
				5.4	2500	13500					subtotal	12.9	193.49	6.30	6	0.30
				6.0	3000	18000
				6.9	1750	12075
						43575	total
												5.4
												5.4
												6.9
											subtotal	17.7	141.02	0.30	0	0.30
			you can see from the table on the right
			that by cutting into pieces 17.7 inches long
			you only waste 0.3 inches per length
			and there is no waste
			from 6.0 inch pieces

<colgroup><col span="15"><col><col span="2"></colgroup><tbody>
</tbody>

 

[oldbrewer]

2496
	waste check
5.4	0.22
6.0	0.00
6.9	0.74
5.4
6.9
12.3	0.93
5.4
5.4
6.9
17.7	0.02
5.4
5.4
5.4
6.9
23.1	0.05
5.4
6.9
6.9
19.2	0.00
by cutting lengths of 19.2 inches
you achieve zero waste

<colgroup><col span="4"></colgroup><tbody>
</tbody>

 

[webuxer]

		length of stock pipe	208	feet
				2496	inches
		part lengths required (inches)								parts per 208 feet length
					quan	length								WASTE	WASTE USE	FINAL WASTE
				5.4	12	64.8						5.4	462.22	1.20	0	1.20
				6.0	12	72						6.0	416.00	0.00	0	0.00
				6.9	16	110.4						6.9	361.74	5.10	0	5.10
						247.2	total
												5.4
												6.9
											subtotal	12.3	202.93	11.40	11.4	0.00
												5.4
												6.0
											subtotal	11.4	218.95	10.80	6.9	3.90
		length of stock pipe	208	feet	pieces in stock	19
				2496	inches	stock inches	47424
		part lengths required (inches)								6.0
					quan	length						6.9
				5.4	2500	13500					subtotal	12.9	193.49	6.30	6	0.30
				6.0	3000	18000
				6.9	1750	12075
						43575	total
												5.4
												5.4
												6.9
											subtotal	17.7	141.02	0.30	0	0.30
			you can see from the table on the right
			that by cutting into pieces 17.7 inches long
			you only waste 0.3 inches per length
			and there is no waste
			from 6.0 inch pieces

<tbody>
</tbody>

Hi, thank you soo much for replying. I believe this is what I need. How can I download your excel file and text it with my data? Thanks

 

[oldbrewer]

PM me your email address and I will send it

 

[webuxer]

Thank you, I just did.