+ Reply to Thread
Results 1 to 8 of 8

Solver setup for list cutting

  1. #1
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Solver setup for list cutting

    The Excel Solver a tool in solving particular problem has its build in limitations when it comes to the size of the problem.

    One way to overcome the size problem is to break down the big problem in several small parts and solve these as stand alone. Then add all these bits and hope that the sum of these reflects the true solution of the big problem.

    But how sure is it that the sum of a number of small solutions where a few factors are decided at a time as opposed to the solution where all factors are taken in account at the same time should give the same answer?

    Looking at an example where there is a production of a number of lists of different length to be made from a standard list of given length and the wastage should be minimized I’ve set up two models. Sheet “Solver_suboptimal” uses a macro to loop solver until the demand of lists is met.

    The other sheet “All_in_one_go” I’ve tried to model all the possible combinations of lists that adds up to standard length or less where solver selects what combinations that makes up the total demand with a minimum wastage.

    To run the macro “SolverLoop” the sheet “Solver_suboptimal” must be the active sheet and a reference to solver must be set in “Visual Basic”

    Developers “Tab” -> Click “Visual Basic” icon -> “Tools” -> “References” and tick box “Solver”. Click “OK” and close “Visual Basic” Window.

    To run solver on sheet “All_in_one_go” just click “Data” tab, select solver and click button “Solve”. After 15 seconds a message will pop up saying “The maximum time was …..” click stop to get the result as solver is not sure it has found it but in the bottom left corner you see the text “Incumbent” and a number and this number stabilizes after 4 – 5 seconds so solver has found a solution but not absolutely sure.

    Comparing the solutions the suboptimal one needs 378 stock rod to meet demand with a waste of 260920 as compared to all_in_one_go needing 364 stock rods producing a waste of 175264.

    If the number of lists to produce becomes less then the difference between these two setups becomes less but this is difficult to predict unless bot setups are tested.

    To set up all combinations is quite a tedious process and if a list length is changed the the whole setup must be rewritten. To see how the formulas are made write 1 for each cell in range L4 to L54.

    The “Solver_suboptimal” is very easy to set up and a change in list length will not matter but for a big number of lists it will be as accurate as the “All_in_one_go”.

    Finally the is a small walk-through of the macro used.

    Alf
    Attached Files Attached Files

  2. #2
    Forum Contributor Gregor y's Avatar
    Join Date
    10-24-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2010 32-Bit
    Posts
    274

    Re: Solver setup for list cutting

    If you're really using solver to that extent you may also want to consider paying the original author for the full version... https://www.solver.com/analytic-solver-platform
    If it'd make you feel better using my answer because of my street cred, then you can go ahead and click Add Reputation below to improve it.

  3. #3
    Registered User
    Join Date
    09-14-2018
    Location
    China
    MS-Off Ver
    Office 2010
    Posts
    41

    Re: Solver setup for list cutting

    Interesting topic. Thanks Alf, you are an expert about Solver!
    Last edited by AliGW; 08-06-2020 at 01:14 AM. Reason: Please don’t quote unnecessarily!

  4. #4
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Solver setup for list cutting

    You are welcome and thanks for kind comments.

    This was not meant to end up in the "Tips and Tuorials" forum. It was originally posted in the "Development testing forum" as a comments to a poster who wanted to know a bit more about solver. But later I found it was mover to this forum. Reading it today I think it should be rewritten but I'm not sure I'm up to it.

    But there are a number of forum members who could pool their "resources" and write a worthwhile tutorial on the how and the why of solver. I'm thinking about members like shg, Mr.Shorty, Kaper and Francesco. There are probably more but these have impressed me with their mathematical know-how and skill in model building.

    I'll contact somebody in admin and see what they think of this and if they can arrange this.

    Alf

  5. #5
    Registered User
    Join Date
    09-14-2018
    Location
    China
    MS-Off Ver
    Office 2010
    Posts
    41

    Re: Solver setup for list cutting

    Thanks for the comment Alf.
    I have one idea about the Cut Stock solution.
    The total number of cutting mode for a list of demand rods may be a huge number if the type of rod demand increase.
    I am using a random cutting mode algorithm to generate random cuts. The steps are as below:
    1) Loop through all the rod, add constraints that each rod must not be less than 1 ;
    for example, we have 4 rods in demand, 3500, 5000, 2000, 1500, and demand 40, 25, 23, 8 respectively. The standard material is 6000, so we need to loop as below:
    a. generate 10(or other bigger number) cuts which first rod(3500) must not be less than 1; these 10 cuts are using solver to determine with objective function like =SUMPRODUCT(cut_mode, rand numbers) by minimize the objective function;
    b. generate 10 cuts including second rod(5000).. note these cuts may be duplicated, and we will remove duplicated cuts afterwards.
    c. generate 10 cuts including third rod(1500)..
    2) Remove the Duplicated Cut Modes
    3) Put the generated cut modes into, set up solver with decision variables : number of rods for each cut mode, and maximize the number of rods used, constraints in the actual cut rods are meeting demand.
    by using this method, I can obtain the optimal solution 364 standard rods most of the time. As I use random numbers, sometimes I only got 370 rods or 378 rods.
    Last edited by AliGW; 08-06-2020 at 01:15 AM. Reason: Please don’t quote unnecessarily!

  6. #6
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Solver setup for list cutting

    Your idea sounds very interesting. Could you please upload a file so I can have a chance to studi it in more detail?

    It seems to that there are more questions about solver today as it was 2 - 3 years ago. Perhaps we could have postings with several solver models. Perhaps one posting for each kind.

    So under title "Knapsack problem with Solver" you would find one or two files showing knapsack problem with a bit of comments. Then one for "Cutting Stock problem with Solver" and so forth.

    Alf

  7. #7
    Registered User
    Join Date
    09-14-2018
    Location
    China
    MS-Off Ver
    Office 2010
    Posts
    41

    Re: Solver setup for list cutting

    See the attached file.
    Please use the rev.2 file.
    Last edited by astupig; 08-06-2020 at 09:19 PM.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Solver setup for list cutting

    Administrative Note:

    @astupig

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 8
    Last Post: 02-12-2020, 01:19 PM
  2. Simple Open Solver Setup Not Working
    By chicagoland8 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-02-2018, 12:44 AM
  3. Optimizing a cutting list for least waste
    By simplemind2979 in forum Excel General
    Replies: 11
    Last Post: 08-08-2016, 04:14 AM
  4. Replies: 6
    Last Post: 05-01-2016, 04:41 AM
  5. Optimize Cutting Material list
    By Suraj3825 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-23-2013, 02:14 PM
  6. How to setup excel spreadsheet with SOLVER with data given as below
    By Jello1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-08-2012, 12:32 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1