linerar/integer programming problem

  • Thread starter Thread starter ben h
  • Start date Start date
B

ben h

Hello, from maths pygmy.

I can describe this problem in words, but not put it into any mathematical
form. Can someone help by suggesting a method for solving it with xl
spreadsheet?

A client of mine sells lengths of aluminium window parts at 6.5 m length.
He has customers who want to buy it at various lengths, in various
quantities, and want to reduce the amount of wastage. E.g.:
30 lengths at 1 m, 25 at 1.5, 25 at 2. How can you work out the smallest
amount of 6.5 m lengths to buy to achieve this? (i.e. the least amount of
wastage)


Sorry for the lameness of the question, but he's a good client and thinks
I'm a whizz with computers just because I design his web-site. I hate to
disappoint.
 
Well, i'm not really shure ....

but what about the formular:
int(quantity * length / 6,5) + 1 = count of 6,5 pieces.

Implement this formular in your spreadsheet.
Does it meet your ideas ?

Manfred
 
I think the best way to do this is to use Excel's Solver. I see twelve
patterns that total between 5.5 and 6.5. In A1:A12, enter a name for each
pattern like P1, P2, etc.

The next column to the right of these cells will be your integer changing
cells.
The next 3 Columns contain the number of each cut that pattern holds.

For example, have column C hold the number of 1 cuts, Column D hold the 1.5
cuts, and column E hold the 2.0 cuts.

For P1, it contains zero 1 & 1.5 m lengths, but has three 2 m lengths.
The second row would have two 1 m cuts, and two 2 m cuts. Etc.

You want to use Sumproduct on the changing Integer cells and each column of
cuts.

What you are doing is having Solver pick how many of each of the twelve
patterns it is to cut. You want to minimize the total used. You need to
add the three constraints in that you need to produce at least the number of
required lengths. The three constraints are your SumProduct that must be
greater or equal to the total needed of 1, 1.5, and 2 respectfully.

The first run shows a total of "19" -> 6.5 m lengths to use. This was
accomplished by using 5 different patterns. There was 5 extra pieces of 1.0
m lengths remaining.

I could not get it down to using 18 pieces of material, so you now try to
improve on using 19 pieces.



Since there is going to be waste anyway using 19 pieces, I changed the
constraint for the 2 m. Instead of requiring >= 25, I made the constraint
= 27.
Click to expand...

This did two things. It still required 19 pieces, but the total number of
patterns was reduced to 3. Having extra 2m pieces is probably better than
having 1m pieces. The 3 extra 2 m pieces can now be used as standby
material to replace any defective 1, 1.5 or 2 if required.


14 Pattern 4's 1->1 m, 1-1.5 m, and 2->2m
3 Pattern 10's 2->1 m, 3-1.5 m, and 0->2m
2 Pattern 12's 5->1 m, 1-1.5 m, and 0->2m


The twelve patterns (1-12) are:

{2, 2, 2},
{1, 1, 2, 2},
{1, 1.5, 1.5, 2},
{1, 1.5, 2, 2},
{1.5, 1.5, 1.5, 1.5},
{1.5, 1.5, 1.5, 2},
{1, 1, 1, 1, 2},
{1, 1, 1, 1.5, 1.5},
{1, 1, 1, 1.5, 2},
{1, 1, 1.5, 1.5, 1.5},
{1, 1, 1, 1, 1, 1},
{1, 1, 1, 1, 1, 1.5}


It's hard to explain in words. HTH though.

Dana
 
Hi ben,
PLUS 1D - Bar nesting software for 1D stock cutting applications
like cutting of pipes, bars, channels, extrusions, lengths etc,
would be perfect for the job.

There is a demo verison, that should be sufficient for small problems.

http://www.nirvanatec.com/downloadcenter.html

hope it helps,
cheers,
s.shrikanth
 
Back
Top