Solver - unsolved mystery!

[jerschwab]

Hi, I'm new to the forum but have been using Excel for years...

Something I only so every do often is use the Solver tool. Up unti
now, it's worked for what I need it to do but now the problem is a bi
more complicated and Solver doesn't like my inputs!

I'm sure it's something simple, but was hoping for some tips to guid
me along!


I'm working on setting the optimum widths for manufactured fabric..
TRIM is the potential lineal edge scrap we have to take to use 99" wid
fabric to make 96" wide material. Our main constraint here is that w
have to have at least 3" of edge trim.

I would like to calculate, based on our history, the best set of width
to use to handle our orders.

The only problem is that, it is very easy for me to find a bette
solution than solver... just change the 142 output value to 141. O
change all the values and watch solver choke. Maybe it's because I hav
so many vertical lookups. Not sure.


Any help is greatly appreciated! I have read the help file twice
searched around the web for a few hours yesterday... and ultimatel
ended up here! I hope I came to the right place

 

[jerschwab]

Oops... forgot the file. Here it is

+----------------------------------------------------------------
| Attachment filename: qryscrimanalysistemp.zip
|Download attachment: http://www.excelforum.com/attachment.php?postid=375122
+----------------------------------------------------------------

 

[Kevin Stecyk]

Jerschwab,

I had a look at your spreadsheet, which is rarely done because of unknown
viruses, malicious vba code etc.

I think your problem is not with the lookups, but rather with the mins and
maxes. I had something similar before, and I don't believe that solver
likes a lot of mins and maxes as constraints.

Rather than having one long column of numbers to be a min or max, you might
want to set every condition to a min or max. Yes, a lot more contraints.
Otherwise, I think Solver has trouble identifying cause and effect. As it
changes numbers, things will appear to change somewhat randomly from
Solver's perspective.

But even changing every condition might not help.

Excel and solver might not be the best tool for this application. You might
want to try a "linear program" program such as Lindo or something.

This message is not of much assistance to you, except that I have
highlighted one potential problem.

Regards,
Kevin

Oops... forgot the file. Here it is!


+----------------------------------------------------------------+
| Attachment filename: qryscrimanalysistemp.zip |
|Download attachment: http://www.excelforum.com/attachment.php?postid=375122|
+----------------------------------------------------------------+

------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to

creating financial statements

 

[Dana DeLouis]

My guess is that it is the use of "VLookup" that is causing the problem.
"Usually," this indicates that you need to rethink the solution. The
general idea of the problem is this... As Solver adjust a value for the
Lookup value, say from 95 to 98, (using your table) Solver notices that the
Target cell is not changing. The Lookup value is not changing, so the
Target is not changing either. Solver very quickly gives up the moment it
notices the output is not changing.

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

Oops... forgot the file. Here it is!


+----------------------------------------------------------------+
| Attachment filename: qryscrimanalysistemp.zip |
|Download attachment: http://www.excelforum.com/attachment.php?postid=375122|
+----------------------------------------------------------------+

------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to

creating financial statements