Solver help needed please

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.
Book1
ABCDEFGHIJKL
1ITEM NUMBERQUANTITYWEIGHT PER PALLETTOTAL WEIGHT
234200811230022000342008342008230023004600
334200910200020000342008342008230023004600
4342008342008230023004600
5342008342008230023004600
634200834200823002300460024150
73420080230002300
834200934200920002000400021150
9342009342009200020004000
10342009342009200020004000
11342009342009200020004000
12342009342009200020004000
13
14TOTALMAX WEIGHT
154530044000
Sheet1
 
Last edited:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Forum statistics

Threads
1,136,303
Messages
5,674,970
Members
419,537
Latest member
ucatchy

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