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

weelrdeelr

New Member
Joined
Nov 24, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
IJKLM
1Example 1 (Should Be Cut)Example 2 (Should Not Be Cut)
2Length of Tube (In)25.25Length of Tube25.25
3Width of Poster (In)22Width of Poster24
4Weight of Poster (Oz)4.3Weight of Poster3.28
5
6TubesWeight (Oz)TubesWeight
7T111.5T111.5
8T211.75T211.75
9T312T312
10T412.2T412.2
11T512.4T512.4
12T612.6T612.6
13T712.8T712.8
14T813T813
15T913.25T913.25
16T1013.5T1013.5
17
18Tube Needed???Tube Needed???
19Desired TubeT9Desired TubeT6
20
21How Much To Cut???How Much To Cut???
22Desired Amount3.00Desired Amount0
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!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,292
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Oct 30, 2014
Messages
4,352
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try this, not quite sure your criteria to cut

Book1
ABCDE
1Example 1 (Should Be Cut)Example 2 (Should Not Be Cut)
2Length of Tube (In)25.25Length of Tube25.25
3Width of Poster (In)22Width of Poster24
4Weight of Poster (Oz)4.3Weight of Poster3.28
5
6TubesWeight (Oz)TubesWeight
7T111.5T111.5
8T211.75T211.75
9T312T312
10T412.2T412.2
11T512.4T512.4
12T612.6T612.6
13T712.8T712.8
14T813T813
15T913.25T913.25
16T1013.5T1013.5
17
18Tube NeededT1Tube NeededT6
19Desired TubeT9Desired TubeT6
Sheet1
Cell Formulas
RangeFormula
B18,E18B18=INDEX(A7:A16,MATCH(MAXIFS(B7:B16,B7:B16,"<="&(15.98-B4)),B7:B16,0))
 

weelrdeelr

New Member
Joined
Nov 24, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
try this, not quite sure your criteria to cut

Book1
ABCDE
1Example 1 (Should Be Cut)Example 2 (Should Not Be Cut)
2Length of Tube (In)25.25Length of Tube25.25
3Width of Poster (In)22Width of Poster24
4Weight of Poster (Oz)4.3Weight of Poster3.28
5
6TubesWeight (Oz)TubesWeight
7T111.5T111.5
8T211.75T211.75
9T312T312
10T412.2T412.2
11T512.4T512.4
12T612.6T612.6
13T712.8T712.8
14T813T813
15T913.25T913.25
16T1013.5T1013.5
17
18Tube NeededT1Tube NeededT6
19Desired TubeT9Desired TubeT6
Sheet1
Cell Formulas
RangeFormula
B18,E18B18=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

New Member
Joined
Nov 24, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
try this, not quite sure your criteria to cut

Book1
ABCDE
1Example 1 (Should Be Cut)Example 2 (Should Not Be Cut)
2Length of Tube (In)25.25Length of Tube25.25
3Width of Poster (In)22Width of Poster24
4Weight of Poster (Oz)4.3Weight of Poster3.28
5
6TubesWeight (Oz)TubesWeight
7T111.5T111.5
8T211.75T211.75
9T312T312
10T412.2T412.2
11T512.4T512.4
12T612.6T612.6
13T712.8T712.8
14T813T813
15T913.25T913.25
16T1013.5T1013.5
17
18Tube NeededT1Tube NeededT6
19Desired TubeT9Desired TubeT6
Sheet1
Cell Formulas
RangeFormula
B18,E18B18=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

New Member
Joined
Nov 24, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,490
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
ABCDEFGH
1Example 1 (Should Be Cut)Example 2 (Should Not Be Cut)Maximum Combined weight
2Length of Tube (In)25.25Length of Tube25.2515.98
3Width of Poster (In)22Width of Poster24
4Weight of Poster (Oz)4.3Weight of Poster3.28Tube must be longer than poster by
51.25
6
7How much to cut?2How much to cut?0TubesWeight
8Tube6Tube6T111.5
9Cut down Tube weight11.6019802Cut down Tube weight12.6T211.75
10Total weight15.9019802Total weight15.88T312
11T412.2
12T512.4
13T612.6
14T712.8
15T813
16T913.25
17T1013.5
Sheet17
Cell Formulas
RangeFormula
B7,E7B7=B2-B3-$G$5
B8,E8B8=MAX(IF($H$8:$H$17*(B2-B7)/B2+B4<$G$2,SEQUENCE(10)))
B9,E9B9=INDEX($H$8:$H$17,B8)*(B2-B7)/B2
B10,E10B10=B9+B4
 
Solution

Forum statistics

Threads
1,175,498
Messages
5,897,768
Members
434,675
Latest member
suraj01

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