Closed Thread
Results 1 to 27 of 27

Optimizing a cutting list for least waste

  1. #1
    Registered User
    Join Date
    01-23-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    4

    Smile Optimizing a cutting list for least waste

    Imagine a whole box of lengths of aluminium sections for making windows.

    To make those windows I need to cut these lengths into smaller sizes.

    I need to do this economically.

    How can I use Excel to help me make this a reality.

    Firstly you would take a big size out of it and then another size out of the offcut piece.

    And then another size out of the offcut left over.

    I need to know how many full stock lengths (6500) long it will take to cut all the pieces listed above.

    Is it possible?

    You need to take into account the quantity of each size and fit it all in together for the most optimal outcome.

    See attached example of possible layout.
    Attached Images Attached Images

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Optimizing a cutting list for least waste

    How did the teacher suggest you approach the problem? What functions are you supposed to include? Or is this a free form exercise?
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Optimizing a cutting list for least waste

    Nice picture, but a spreadsheet would be better!
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Optimizing a cutting list for least waste

    Hi Mrnuddles and welcome to the forum,

    I've been noodling on your problem a while and I really like it. I've written some code to solve it.
    See the attached spreadsheet. Click the button and watch it guess your answer 20 times. It will copy to the range on the right the best guess.

    I took a math class on "How to Solve It" http://en.wikipedia.org/wiki/How_to_Solve_It which said you need to make a guess for an answer as a start. If your guess is wrong then you make another guess, until/if you can come up with a formula.

    The attached never gets to the formula part but simply makes guesses and keeps track of how many pieces of stock are needed with random cutting of slices. If you ever come up with a number of 49 or 48 I'd stop and use that as my answer.

    My formula might be called answer by exhaustion but computers don't get tired, do they?

    I got a 49 peices after about 4 tries!!!

    hope this helps
    Attached Files Attached Files
    Last edited by MarvinP; 01-24-2011 at 12:34 PM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Registered User
    Join Date
    01-23-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Optimizing a cutting list for least waste

    Quote Originally Posted by MarvinP View Post
    Hi Mrnuddles and welcome to the forum,

    I've been noodling on your problem a while and I really like it. I've written some code to solve it.
    See the attached spreadsheet. Click the button and watch it guess your answer 20 times. It will copy to the range on the right the best guess.

    I took a math class on "How to Solve It" http://en.wikipedia.org/wiki/How_to_Solve_It which said you need to make a guess for an answer as a start. If your guess is wrong then you make another guess, until/if you can come up with a formula.

    The attached never gets to the formula part but simply makes guesses and keeps track of how many pieces of stock are needed with random cutting of slices. If you ever come up with a number of 49 or 48 I'd stop and use that as my answer.

    My formula might be called answer by exhaustion but computers don't get tired, do they?

    I got a 49 peices after about 4 tries!!!

    hope this helps
    Hi there,
    Wow I am impressed.
    It works well, I have a downloaded but ugly shareware optimizing program which I cannot integrate into my spreadsheet that gets 51 lengths as a best result, so well done.
    I really like how this runs, my only question is-
    How do I see what calculations are taking place so I can recreate this within my existing spreadsheet?
    I am assuming you used the Solver add in?
    Can I see a screenshot maybe of what you input into it?
    I have been using Excel for many years and I have some huge workbooks (60 sheets) that I use for estimating in my line of work but, I am new to using these types of complicated equations, my knowledge stops at What-If-Statements.
    Thanks a lot.
    Regards

    Mrnuddles

  6. #6
    Registered User
    Join Date
    01-23-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Optimizing a cutting list for least waste

    Hi, this is not a teaching thing at all, I am needing this problem solved for my work to help me create a larger workbook for optimizing cutting lists.
    Regards

    Mrnuddles

  7. #7
    Registered User
    Join Date
    01-23-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Optimizing a cutting list for least waste

    OK, so sorry to say, but I have hit a snag.
    The spreadsheet crashes if you increase the quantities of each item by 100 or so.
    I did drag down the columns G and R to help calculate the larger numbers.
    Help.

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Optimizing a cutting list for least waste

    There's a simple formula-based cut list at http://www.box.net/shared/uhrjy318l1
    Entia non sunt multiplicanda sine necessitate

  9. #9
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Optimizing a cutting list for least waste

    Yes - I was afraid you would want a generalized solution. The workbook and code I gave you was just a quick example of how I'd do that specific problem. I need to look at the code and my absolute ranges and numbers to make it more generalized.

    I did not use the Solver in any way!

    I simply started chopping your 6500 mm stock by random lenghts that you had noted. When I cut one I'd subtract one of that lenght from the list. I'd put that length in a Stock# row and Cut# Column based on if it had hit the 6500 number yet.

    I did this problem more for a proof of concept than a generalized solution. To make it more generalized, I'd need to know what stock length(s) you have and how many different lenghts are needed by a customer (maximum for all customers, I guess)

    The best answer (the one using the least number of Stock Lengths) is copied to Column I for your view. It stays there until another try gives a less stock length number.

    If you can supply a more generalized sheet that had the above, I'd look at it.

    It is interesting that another program gave 51 for the best answer. I wonder what shg's linked program would give?

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Optimizing a cutting list for least waste

    I wonder what shg's linked program would give
    I just looked at that -- it's bad for these lengths, because they are large compared to the stock length.

  11. #11
    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: Optimizing a cutting list for least waste

    This is a solver example of a cutting list. Perhaps you can modify it to fit your needs?

    Alf
    Attached Files Attached Files

  12. #12
    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: Optimizing a cutting list for least waste

    With time on my hands I set up a solver solution with cut length as stated by the OP.

    Don't know if I found all possible combinations for cutting but managed to find 63 possible ways.

    The optimal solution of 47 lengths of aluminum stock is probably a theoretical solution because in real life one must probably take in account the "thickness" of each cut.

    For example cut 55 produces 2 length of 1750 mm and 2 lengths of 1500 mm this all adds up to 6500 mm. But what if cut "thickness" is 0,5 mm? That is a "length" loss of 1,5 mm.

    Can this be ignored or should the cut length of the different pieces be modified?

    Alf
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    02-18-2011
    Location
    Norwich
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Optimizing a cutting list for least waste

    I am also needing a speadsheet in cutting aluminium door profiles for multiple doors of different sizes. The first spreadsheet looks very promising although I get an error when running the macro.

    I get runtime error 438

    When I press debug the following line of code is highlighted

    extCutRow = WorksheetFunction.RandBetween(2, 9)

    I wonder if you came up with a solution that you would be able to share with me, would be greatly appreciated.

    Thanks

  14. #14
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Optimizing a cutting list for least waste

    I believe you need to have the Analysis Tool Kit add-in for Excel 2003 to perform the RandBetween. The function was included in 2007 and 2010 as standard.

  15. #15
    Registered User
    Join Date
    02-18-2011
    Location
    Norwich
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Optimizing a cutting list for least waste

    Thank you for the quick reply. The addin is activated although still the same error, i've tried on another computer with Excel 2007 and it works fine though.

  16. #16
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Optimizing a cutting list for least waste

    Try to just put
    Please Login or Register  to view this content.
    in any cell as a formula and see if the old version knows what to do with it.
    That would simplify and solve the mystery.

  17. #17
    Registered User
    Join Date
    11-01-2012
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Optimizing a cutting list for least waste

    This is exactly what I was looking for online! I'm not nearly as advanced at Excel as most of you, but I was hoping to be able to figure out a way to do the exact same thing for handle lengths using metal 78" long.
    I'm just not sure how to alter that spreadsheet to work for me. I have 151 pieces needed and we're trying to figure out how to optimize it as well as know which pieces get cut from the same length.

  18. #18
    Registered User
    Join Date
    02-18-2011
    Location
    Norwich
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Optimizing a cutting list for least waste

    Hi Sarah.

    If you want to Private message me your email address I can send you a sheet I have made with some instructions.

  19. #19
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Optimizing a cutting list for least waste

    vandango05,

    You can upload the file here.

    To Attach a File:

    1. Click on Go Advanced
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  20. #20
    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: Optimizing a cutting list for least waste

    @ SahraMascara: Hi

    Since this thread contains several suggested solution you must tell which solution you think should work for you. I would also suggest you upload a file describing stock length and the size and number of the pieces you need.

    Alf
    Last edited by Alf; 11-01-2012 at 01:01 PM.

  21. #21
    Registered User
    Join Date
    11-01-2012
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Optimizing a cutting list for least waste

    Basically the one that MarvinP posted for Mrnuddles was almost perfect, except that it didn't give me enough spaces for all the different sizes I have for my lengths in the top section, and for some reason the cuts listed do not keep it within my 78" length limit, which is what the metal we're ordering for our handles comes in.

  22. #22
    Registered User
    Join Date
    02-15-2014
    Location
    Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Optimizing a cutting list for least waste

    Dear Alf,

    This seems super efficient. Could you please tell me how you found the 63 possible combinations?

    Thanks,
    Fahim.

    Quote Originally Posted by Alf View Post
    With time on my hands I set up a solver solution with cut length as stated by the OP.

    Don't know if I found all possible combinations for cutting but managed to find 63 possible ways.

    The optimal solution of 47 lengths of aluminum stock is probably a theoretical solution because in real life one must probably take in account the "thickness" of each cut.

    For example cut 55 produces 2 length of 1750 mm and 2 lengths of 1500 mm this all adds up to 6500 mm. But what if cut "thickness" is 0,5 mm? That is a "length" loss of 1,5 mm.

    Can this be ignored or should the cut length of the different pieces be modified?

    Alf

  23. #23
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Optimizing a cutting list for least waste

    fahim, welcome to the forum

    Perhaps you missed the thread immediately above yours?

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  24. #24
    Registered User
    Join Date
    01-09-2014
    Location
    ontario
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: Optimizing a cutting list for least waste

    I am hoping this is posted in here correctly. I am having a similar problem with teh calculations for cutting material. I am planning out making some wooden patio furniture this spring. I want to lay out all the board and cut them for my project. It would halp to know what needs to be cut from what to reduce waste as i will make it the furniture from cedar.

    I have attached what i could come up with so far. I want the spreadsheet to cut parts from the drop pieces. Or if there isnt enough to cut from the stock length boards. The area highlighted in red is where io tried to use the IF statements and such. I have tried the spreadheets posted on here. but I cant get them to work right for me. Any help would be very appreciated.

    https://www.dropbox.com/s/3je6k8s07i...%20NEEDED.xlsx

  25. #25
    Registered User
    Join Date
    11-05-2023
    Location
    Sydney, Australia
    MS-Off Ver
    365 v 2310
    Posts
    1

    Re: Optimizing a cutting list for least waste

    I Like it. But one comment. You have hardcoded the Stock length to 6500.
    Once I changed that to the length for my requirements, it worked well.

    Thanks

  26. #26
    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,348

    Re: Optimizing a cutting list for least waste

    Thanks, but you may not have noticed that the thread is over 12 years old.
    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.

  27. #27
    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: Optimizing a cutting list for least waste

    I would also like to add some comments to this thread.

    In the uploaded file (post #12) there is an formula error in cell L62 i.e. "=IF(M67<=0,000001;0;$B$5-$J$5*J67-$K$5*3)". The proper formula are "=IF(M67<=0,000001;0;$B$5-$J$5-$K$5*3)" as J67 is a fixed value of 1. This will make it possible to change the solver setting from "GRG Nonlinear" to "Simplex LP"

    Another problem with the uploaded file in post #12 is that the file was originally written for Excel 2003 so with Excel 2010 (probably Excel 2007 also) and later version the solver default setting was changed to ignore integers. It's not enough to define a numbers as integers one must also check under solver options that the box marked "Ignore Integer Constraints" are not ticked if the numbers should be treated as integers.

    Making these changes and running the file in an Excel 2010 environment it will still need 47 rods but the wastage will drop from 5600 to 3850.

    Moral of the story:

    Errare humanum est. And old files and their solutions should be regarded with a bit of suspicion and checked before use.

    Alf

Closed Thread

Thread Information

Users Browsing this Thread

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

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