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)
Bookmarks