Re: Solver Help
- From: "Dana" <ddelouis@xxxxxxxxxxxxx>
- Date: Thu, 21 Jun 2007 14:48:19 -0400
how many 12' sticks I would need, to get for example:5- 3' pieces,
2- 4' pieces,
3- 5' pieces, and
1-10' piece.
I think solver might be
able to figure that out for me, but I don't know for sure.
Any thoughts or examples out there? ]
Hi. I'm sure you'll get great responses, so here's just one idea.
Cutting-Stock problems are usually the toughest.
Excel Solver can not usually do these types of problems because of the
complexity, and size of the problem if done "Brute-Force."
One usually runs a secondary program to generate Patterns. The number of
patterns is usually much smaller and more manageable.
For example, Pattern_1 might be to cut your 12' material into two 5-ft
sections with 2' waste.
Another Pattern might be to cut four 3-ft sections with zero waste. Etc.
Do single patterns first. The 10' cut can't be combined with anything else.
Cut your 1-10' section, have 2' scrap, and remove 10' cuts from further
consideration.
You now need to cut {3, 3, 3, 3, 3, 4, 4, 5, 5, 5}
For this problem, I might run it through a Subset Program and look at all
2047 Subsets. (2^11-1 = 2047).
This is a small size, and can be done quickly.
Send the output to Excel's Dictionary object to remove duplicates. now you
are only looking at 143 Patterns.
Next, remove patterns that Total 9 and less, or greater than 12.
You remove patterns that total 9 or less because you can always add another
3' section and still be <= 12. Therefore, these patterns are a waste for
consideration.
Now, you've got only 6 patterns, and this is much easier to work with:
P1) {5, 5},
P2) {3, 3, 4},
P3) {3, 3, 5},
P4) {3, 4, 4},
P5) {3, 4, 5},
P6) {3, 3, 3, 3}
Your total requirement is 38'. We note that 3-12' only gives you 36'.
So, your best case now is to use 4 - 12' Materials.
Now, use Solver to pick how many of each pattern above you need.
You now know you will have multiple solutions when you pick a total of 4
patterns from above.
Here is just one of many techniques to go from here. Usually, you try to
minimize waste. I would try to Maximize waste of one of the cuts.
The larger the waste, the more chance you have of being able to cut a
smaller pattern for profit should the need arise.
I would set a constraint to make one more 5' cut for a total of 4.
I would cut
1 pattern of P6 (4-3')
1 pattern of P4 (1-3', 2-4')
and
2 patterns of P1 (2-5')
..
** However, on the second pattern here, just cut one 5' piece. You now
have 3-5' pieces. Don't cut the other 5'. Leave the material at 7' for use
elsewhere
Again, there are many ways to attack this problem.
--
Dana DeLouis
"brownti via OfficeKB.com" <u31540@uwe> wrote in message
news:7409a1f8bf90b@xxxxxx
I have a problem that I think, hope, can be solved with solver, although i
havent used solver enough to know how. I get material in 12' lengths and
then cut those into even foot lengths from 3 feet to 12 feet. My quantity
of
each length continually changes so i would like to set something up that
would determine how many 12' sticks i would need, to get for example: 5-3'
pieces, 2-4' pieces, 3-5' pieces and 1-10' piece. I think solver might be
able to figure that out for me, but i dont know for sure. Any thoughts or
examples out there? Thanks a lot, i really appreciate it.
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-functions/200706/1
.
- Follow-Ups:
- Re: Solver Help
- From: joeu2004
- Re: Solver Help
- From: brownti via OfficeKB.com
- Re: Solver Help
- References:
- Solver Help
- From: brownti via OfficeKB.com
- Solver Help
- Prev by Date: Re: Combining multiple files in Excel
- Next by Date: Re: Need Macro to Open Access
- Previous by thread: Solver Help
- Next by thread: Re: Solver Help
- Index(es):
Relevant Pages
|
