Determining the heaviest tube to package a poster in and how much to cut down the tube by if needed.

[weelrdeelr]

Hi guys! I had a pretty complicated problem that I couldn't quite figure out. Pretty much I need to choose the heaviest tube to package a poster in, and how much to cut down the tube by if needed. We are going to want to use the heaviest tube possible for each poster. If there is enough room to cut down the tube, we're going to want to do so. All the tubes are 25.25 inches long. The maximum weight the tube and poster can be together is 15.98 ounces. The maximum width the poster can be is 24 inches. If the poster is 24 inches, the tube cannot be cut. We're going to want to round the amount we need to cut up to the nearest quarter inch. Below are two examples with explanations showing these possibilities:

The Spreadsheet.xlsx
	I	J	K	L	M
1	Example 1 (Should Be Cut)			Example 2 (Should Not Be Cut)
2	Length of Tube (In)	25.25		Length of Tube	25.25
3	Width of Poster (In)	22		Width of Poster	24
4	Weight of Poster (Oz)	4.3		Weight of Poster	3.28
5
6	Tubes	Weight (Oz)		Tubes	Weight
7	T1	11.5		T1	11.5
8	T2	11.75		T2	11.75
9	T3	12		T3	12
10	T4	12.2		T4	12.2
11	T5	12.4		T5	12.4
12	T6	12.6		T6	12.6
13	T7	12.8		T7	12.8
14	T8	13		T8	13
15	T9	13.25		T9	13.25
16	T10	13.5		T10	13.5
17
18	Tube Needed	???		Tube Needed	???
19	Desired Tube	T9		Desired Tube	T6
20
21	How Much To Cut	???		How Much To Cut	???
22	Desired Amount	3.00		Desired Amount	0
Packaging

Example 1: With a poster weight of 4.3oz, T10 would need us to cut off 3.5 inches. ( 4.3 + 13.5 = 17.8 - 15.98 = 1.82 / 13.5 = 0.1349 * 25.25 = 3.4 = 3.5 ) This is impossible since the print is 22 inches wide. T8 would need us to cut off 2.75 inches which would work but T9 would need us to cut off 3 inches which would also work. We'd want to use T9 since it is the heaviest tube that would work.

Example 2: The easy case. With a poster width of 24 inches, we cannot cut any tube. (The tubes have usable space of 24 inches since they have endcaps). T7 would put us at 16.08 ounces which is over 15.98. T5 would put us at 15.68 ounces which would work but T6 would put us at 15.88 ounces. So we'd want to use T6 since it is the heaviest tube possible.

Thanks so much guys!

 

[RoryA]

A gentle reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Determining the heaviest tube to package a poster in and how much to cut down the tube by.
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[AlanY]

try this, not quite sure your criteria to cut

Book1
	A	B	C	D	E
1	Example 1 (Should Be Cut)			Example 2 (Should Not Be Cut)
2	Length of Tube (In)	25.25		Length of Tube	25.25
3	Width of Poster (In)	22		Width of Poster	24
4	Weight of Poster (Oz)	4.3		Weight of Poster	3.28
5
6	Tubes	Weight (Oz)		Tubes	Weight
7	T1	11.5		T1	11.5
8	T2	11.75		T2	11.75
9	T3	12		T3	12
10	T4	12.2		T4	12.2
11	T5	12.4		T5	12.4
12	T6	12.6		T6	12.6
13	T7	12.8		T7	12.8
14	T8	13		T8	13
15	T9	13.25		T9	13.25
16	T10	13.5		T10	13.5
17
18	Tube Needed	T1		Tube Needed	T6
19	Desired Tube	T9		Desired Tube	T6
Sheet1

Cell Formulas
Range	Formula
B18,E18	B18	=INDEX(A7:A16,MATCH(MAXIFS(B7:B16,B7:B16,"<="&(15.98-B4)),B7:B16,0))

 

[weelrdeelr]

try this, not quite sure your criteria to cut

Book1
	A	B	C	D	E
1	Example 1 (Should Be Cut)			Example 2 (Should Not Be Cut)
2	Length of Tube (In)	25.25		Length of Tube	25.25
3	Width of Poster (In)	22		Width of Poster	24
4	Weight of Poster (Oz)	4.3		Weight of Poster	3.28
5
6	Tubes	Weight (Oz)		Tubes	Weight
7	T1	11.5		T1	11.5
8	T2	11.75		T2	11.75
9	T3	12		T3	12
10	T4	12.2		T4	12.2
11	T5	12.4		T5	12.4
12	T6	12.6		T6	12.6
13	T7	12.8		T7	12.8
14	T8	13		T8	13
15	T9	13.25		T9	13.25
16	T10	13.5		T10	13.5
17
18	Tube Needed	T1		Tube Needed	T6
19	Desired Tube	T9		Desired Tube	T6
Sheet1

Cell Formulas
Range	Formula
B18,E18	B18	=INDEX(A7:A16,MATCH(MAXIFS(B7:B16,B7:B16,"<="&(15.98-B4)),B7:B16,0))

Thanks! That works great for Example 2, but obviously not for the first example. If the poster width is less than 24 inches, we'll be cutting. That's the criteria I suppose. It doesn't appear what you wrote accounts for any cutting in the first example. It just gives the tube that weighs closest to 15.98 minus the poster weight.

 

[weelrdeelr]

try this, not quite sure your criteria to cut

Book1
	A	B	C	D	E
1	Example 1 (Should Be Cut)			Example 2 (Should Not Be Cut)
2	Length of Tube (In)	25.25		Length of Tube	25.25
3	Width of Poster (In)	22		Width of Poster	24
4	Weight of Poster (Oz)	4.3		Weight of Poster	3.28
5
6	Tubes	Weight (Oz)		Tubes	Weight
7	T1	11.5		T1	11.5
8	T2	11.75		T2	11.75
9	T3	12		T3	12
10	T4	12.2		T4	12.2
11	T5	12.4		T5	12.4
12	T6	12.6		T6	12.6
13	T7	12.8		T7	12.8
14	T8	13		T8	13
15	T9	13.25		T9	13.25
16	T10	13.5		T10	13.5
17
18	Tube Needed	T1		Tube Needed	T6
19	Desired Tube	T9		Desired Tube	T6
Sheet1

Cell Formulas
Range	Formula
B18,E18	B18	=INDEX(A7:A16,MATCH(MAXIFS(B7:B16,B7:B16,"<="&(15.98-B4)),B7:B16,0))

Would it be possible to have it start "checking" the tubes heaviest first, instead of lightest first?

 

[weelrdeelr]

Oh, we'll also want to cut the maximum amount possible. That's why Example 1 has a desired amount of 3.00. That'll put the tube at 22.25 inches long, enough the fit the poster. T10 would need us to cut too much, and T8 wouldn't have us cut "enough", since we can cut more off of a heavier tube and still make weight. The goal is to use the heavy tubes first, and we do that by cutting off as much as possible. Thank you!!!

 

[Eric W]

It seems to me that you want to figure out how much to cut first. Once you figure out how much to cut, you can figure out the adjusted weights of your tubes, then just check all of them until you go over your weight limit. Something like this:

Book1
	A	B	C	D	E	F	G	H
1	Example 1 (Should Be Cut)			Example 2 (Should Not Be Cut)			Maximum Combined weight
2	Length of Tube (In)	25.25		Length of Tube	25.25		15.98
3	Width of Poster (In)	22		Width of Poster	24
4	Weight of Poster (Oz)	4.3		Weight of Poster	3.28		Tube must be longer than poster by
5							1.25
6
7	How much to cut?	2		How much to cut?	0		Tubes	Weight
8	Tube	6		Tube	6		T1	11.5
9	Cut down Tube weight	11.6019802		Cut down Tube weight	12.6		T2	11.75
10	Total weight	15.9019802		Total weight	15.88		T3	12
11							T4	12.2
12							T5	12.4
13							T6	12.6
14							T7	12.8
15							T8	13
16							T9	13.25
17							T10	13.5
Sheet17

Cell Formulas
Range	Formula
B7,E7	B7	=B2-B3-$G$5
B8,E8	B8	=MAX(IF($H$8:$H$17*(B2-B7)/B2+B4<$G$2,SEQUENCE(10)))
B9,E9	B9	=INDEX($H$8:$H$17,B8)*(B2-B7)/B2
B10,E10	B10	=B9+B4

 

[weelrdeelr]

THANK YOU!!!