+ Reply to Thread
Results 1 to 5 of 5

Is there a formula to distribute a fixed set of integers => a series of target values?

  1. #1
    Registered User
    Join Date
    07-10-2016
    Location
    North Carolina
    MS-Off Ver
    MS Office 2013
    Posts
    2

    Question Is there a formula to distribute a fixed set of integers => a series of target values?

    I'm installing hardwood floors in three bedrooms. The planks are manufactured in 7 standard lengths, and each carton contains a varied mix. An optimal layout would divide the available planks among each row spanning the width of the room with the least excess to be trimmed at the edge. Is there a formula that could evaluate the data set of available plank lengths and distribute them => to the range of target lengths of each row (27 rows are the width of the room, 14 rows extend into the closet) while minimizing the overage to reduce trim waste? I attempted to use Solver but I don't know how produce multiple results which "use up" a fixed quantity of data, vs calculating different ways the same data can be arranged.

    My data set looks like this. Numbers in italics are what I'm hoping to achieve for each row: randomly assigned multiples of each plank length which produce a sum => the length of each row with minimum overage (ex. a constraint of not more than 3" greater than the length of each row. Is this possible? Thanks for any suggestions.



    Identifier Plank A Plank B Plank C Plank D Plank E Plank F Plank G Total
    Quantity 10 25 34 50 38 33 27 217
    Length 12 16 18 24 30 36 42 5794

    Row 01 125 0 0 2 1 1 1 0 126
    Row 02 125 0 0 0 2 0 1 1 126
    Row 03 125 1 0 1 1 1 0 1 126
    Row 04 151 0 2 1 1 0 1 1 152
    Row 05 151
    Row 06 151
    Row 07 151
    Row 08 151
    Row 09 151
    Row 10 151
    Row 11 151
    Row 12 151
    Row 13 151
    Row 14 151
    Row 15 151
    Row 16 151
    Row 17 151
    Row 18 151
    Row 19 151
    Row 20 125
    Row 21 125
    Row 22 125
    (Continues to Row 41)

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Is there a formula to distribute a fixed set of integers => a series of target values?

    I highly doubt there is a single formula. My first impression is that this will be a variation of the common "cutting stock problem". I would probably start my research into this by studying the cutting stock problem until I understood it well enough to adapt it to this specific situation. A couple of pages that I found by putting "cutting stock problem Excel" into my favorite internet search engine:
    http://blog.excelmasterseries.com/20...aste-with.html
    http://www.solver.com/cutting-stock
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    07-10-2016
    Location
    North Carolina
    MS-Off Ver
    MS Office 2013
    Posts
    2

    Re: Is there a formula to distribute a fixed set of integers => a series of target values?

    Thank you, I'll look into this. Finding the correct terminology to define the scenario was the biggest challenge I had in researching a solution.

  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: Is there a formula to distribute a fixed set of integers => a series of target values?

    The excel solver is not up to this but the OpenSolver a freebie developed by the University of Auckland NZ seems to be able to do so.

    Download it at:
    http://opensolver.org/installing-opensolver/

    I've set up a small model for you. You can build your model using the excel solver but run your model with the "OpenSolver" and yes "OpenSolver" integrates nicely with excel.

    Alf
    Attached Files Attached Files

  5. #5
    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: Is there a formula to distribute a fixed set of integers => a series of target values?

    Playing around a bit more with OpenSolver I managed to find a solution that used all planks in carton. The first 3 rows I set manually, all the other rows are calculated by OpenSolver.

    Attachment 485131


    Alf
    Attached Images Attached Images
    Last edited by Alf; 10-17-2016 at 12:54 PM. Reason: Found a better solution.

+ 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: 2
    Last Post: 08-25-2015, 09:57 AM
  2. Replies: 13
    Last Post: 10-08-2014, 08:35 AM
  3. Replies: 7
    Last Post: 06-12-2013, 01:11 PM
  4. IF Formula Query for Integers and Non-Integers
    By mrvp in forum Excel General
    Replies: 3
    Last Post: 03-09-2012, 07:35 PM
  5. fixed upper and lower target line
    By amator in forum Excel General
    Replies: 2
    Last Post: 07-06-2010, 05:12 AM
  6. Random selection from a series of integers
    By damiandd1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-03-2008, 03:35 PM

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