Finding all combinations that make a certain sum
Hello! I am currently building my own patio set and have figure out what sizes of wood pieces I need, but now I am trying to figure out the most efficient way to cut the wood with minimal waste (and how many wood boards to buy). I tried using the Solver function, but it only gives me one possible combination. I used 95 as my desired size ( 95 inches for an 8 foot board so I have one extra inch). If you have any better ideas that would be great!
I've set up a Solver model based on how I understand your problem. I assume you wish to make as many lengths of 95" with a minimum wastage.
This solver model loops through all you lengths util all are used. A wastage of 0 means the lengths adds up to 95" and a wastage of 0,5" means lengths adds up to 95,5"
To run the macro "SolverLoop" you must first set a reference to Solver. Click Visual Basic Icon, Tools-> References and tick box marked Solver.
Alf
As the first model solves the problem one row at the time the result may be sub-optimal i.e. the first result will be the best but as solver processes the rows the choice to
find the best solution gets more and more limited.
The best solution is found by letting solver find a solution for all rows in one go. But this makes the problem to big for the excel solver. That's why I've set up a model using
the "OpenSolver" a freebie build by the University of Auckland NZ.
It seems I uploaded the wrong file i.e. one I've used earlier in solving a similar problem. Have replaced this with one based on the OP's values from his uploaded file. Have tighten the gap a bit to 95 - 97,5 and now it takes about 37 - 38 seconds for "OpenSolver" to find a solution.
Comparing the two files in my post one can see the effect of sub-optimizing as the result in the macro driven file diverge from the optimal solution at the end when solvers choices becomes limited as opposed to the "OpenSolver" solution.
Alf
Last edited by Alf; 08-03-2020 at 04:04 PM.
This is perfect thank you so much! This was exactly what I was looking to do and the results are awesome. I ended up changing the size of the board since I realized the 10 foot boards were cheaper and this worked out perfectly to figure out how many to buy with minimum waste thanks so much for your help!
You are welcome and thanks for feed back.
Alf
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks