babycody
Well-known Member
- Joined
- Jul 8, 2003
- Messages
- 1,395
I am trying to figure out how to use Solver to figure out a problem. I need to put 22,000 pounds on the front of a trailer, and 22,000 pounds on the rear. I want for someone to be able to choose an item number, and the cells next to it would populate with the pallet weight. Then they would type in how many pallets of each item. I would like to use solver to show them where to place each pallet of a particular item on the truck so that the weight is distributed evenly. The catch is the 22,000 lbs on each end, and a maximum weight of 44,000 pounds. If the weight exceeds 44,000 lbs then I want solver to maximize the load weight by keeping as many pallets as possible. Below is a crude setup I was trying. Don't mind the formulas much. They were just something I was messing around with. I really would appreciate some help on this. I just can't wrap my head around where to begin. Perhaps Solver isn't what I need? Maybe a formula solution would be more elegant.
In this example the best solution I believe would be to drop J6 in order to get the front within the 22,000 lb weight range. Note: I can't have two spaces next to each other blank. That would mean I had a gap between pallets, and I can't have that since the pallets could shift. That's why I chose J6.
In this example the best solution I believe would be to drop J6 in order to get the front within the 22,000 lb weight range. Note: I can't have two spaces next to each other blank. That would mean I had a gap between pallets, and I can't have that since the pallets could shift. That's why I chose J6.
Book1 | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | ITEM NUMBER | QUANTITY | WEIGHT PER PALLET | TOTAL WEIGHT | ||||||||||
2 | 342008 | 11 | 2300 | 22000 | 342008 | 342008 | 2300 | 2300 | 4600 | |||||
3 | 342009 | 10 | 2000 | 20000 | 342008 | 342008 | 2300 | 2300 | 4600 | |||||
4 | 342008 | 342008 | 2300 | 2300 | 4600 | |||||||||
5 | 342008 | 342008 | 2300 | 2300 | 4600 | |||||||||
6 | 342008 | 342008 | 2300 | 2300 | 4600 | 24150 | ||||||||
7 | 342008 | 0 | 2300 | 0 | 2300 | |||||||||
8 | 342009 | 342009 | 2000 | 2000 | 4000 | 21150 | ||||||||
9 | 342009 | 342009 | 2000 | 2000 | 4000 | |||||||||
10 | 342009 | 342009 | 2000 | 2000 | 4000 | |||||||||
11 | 342009 | 342009 | 2000 | 2000 | 4000 | |||||||||
12 | 342009 | 342009 | 2000 | 2000 | 4000 | |||||||||
13 | ||||||||||||||
14 | TOTAL | MAX WEIGHT | ||||||||||||
15 | 45300 | 44000 | ||||||||||||
Sheet1 |
Last edited: