Posted by daniel on December 16, 2001 12:02 PM

could someone help me write a formula to work out this:---i have 11 different cuts of carpet all totaling 44 meters, i have only a stock of a 30 meter roll and a 20 meter roll of carpet.
i need excel to work out the cuts to fit the best way into these stock rolls with minimal waste
CAN ANY ONE HELP?

Posted by Jacob on December 16, 2001 4:03 PM

What are the cut sizes you need?

Jacob

Posted by Tom Dickinson on December 16, 2001 8:17 PM

Jacob:
The way I would solve this with a program would take me to long to right at the moment. I would suggest doing the following:

In column A put in the cuts you need in ascending or descending order. In column C and D, row 1, put the two roll sizes you have. In column C row 2 put the following formula:

=IF(B2=1,A2,0)

In column D row 2 put this formula:

=IF(B2&LT;&GT;1,A2,0)

Copy these two formulas down through row 12 (to cover the 11 cuts.

In cell C13 put the following formula:

=IF(SUM(C2:C12)>C1,"####",C1-SUM(C2:C12))

copy this formula to cell D13.

Now you can easily play with the combinations of cuts till you get one that fits. Just put a "1" in column B in the row for a cut(s), and it will show up in column C. Otherwise, it will show up in column D. As long as the sums are numbers, you are within the roll length. (The number is how much is left of the roll after the designated cuts.) If a "###" appears, your cuts are more than what is on the roll.