Solver Constraints...

[Pascal Tomasi]

Hi All,

I would like to use solver to solve a problem I am facing
with at present. I am refitting floorboards in my room
and would like to minimize wasting material.

Floor boards are the same width and come in varyaing
lengths (i.e. I can cut them shorter) and should be laid
down in the same way across the whole room. Furthermore, 3
or more floorboards are required to cover one length of
the room.

Question... My original idea was to have just one area
constraint (i.e. the sum of the area of the boards used
should >= the sum of the area of the room), but then I
thought that this could mean that I would have to lay
floorboards in mixed direction. Then I thought about
having constraints for lentgh and width of the room.
How do I specify the constraints for the length and width
of the room?

Many thanks for your help,

Pascal.

 

[Dana DeLouis]

Hi Pascal. I'm not an expert, but here are some thoughts for discussion.
For a cutting stock problem (similar to the following link example)

http://www.solver.com/proframe.htm

I think the number of possible combinations when setting up the problem may
exceed the 200 changing cell constraints of Excel's Solver. Excel is hard
to work with when the problem gets a little large. Of course, it depends on
your situation, and how many floor boards you have.

One possible workaround may be the following. List all the Floor Boards
you have down 1 column. Set up solver in a loop. Have Solver pick some
boards for 1 length of the room by minimizing waste. Remove the solution
from the list of floor boards. Then Solve again for the next Row. Use
"SumProduct" on the Floor board lengths, and Binary constraints (1 if Solver
picks it, 0 if not).

This may not be the best solution, but it is easy to implement, and is a
workaround for the 200 changing cell limitation. You may also want to add a
constraint that the number of floor boards per solution be <=4 or 5. The
idea is that you may not want Solver to pick a bunch of small pieces for one
Row across the room. It may not look nice.
HTH.

 

[Elliott Alterman]

Don't know about "SOLVER." Can anyone point me to info on it. I have Excel
2000.

TIA

 

[JE McGimpsey]

Well, you could start with "About Solver" in Excel Help...


If you really want to dig in, check out

http://www.frontsys.com/xlhelp.htm

Frontline systems is the maker of Solver.