Solver

[Janus]

Is it possible to have two separate Solver solutions operating in a on
page workbook in Excel 2003? I have two sets of data on on
spreadsheet page both require the use of Solver. The data sets ar
independant of each other. I can set Solver to work with one set onl
and would like to also use Solver on the other set without creating
second worksheet. Thanks in advance, Janu

 

[Morrigan]

Solver has 3 options for finding solution for target cell: maximum
minimum, and value is. 2 separate problems means 2 targe
cells(TargetCell1 and TargetCell2). You can create a new targe
cell(TargetCell3) and do the following:

Now if TargetCell1 and TargetCell2 are to be "maximum":
TargetCell3 = TargetCell1 + TargetCell2, set TargetCell3 to find th
maximum

If TargetCell1 is to be maximum and TargetCell2 is to be "minimum":
TargetCell3 = TargetCell1 - TargetCell2, set TargetCell3 to find th
maximum

If TargetCell1 is to be maximum and TargetCell2 is to be "value is":
TargetCell3 = TargetCell1 - TargetCell2, set TargetCell3 to find th
maximum

You can figure out the other combinations.


Hope it helps.

 

[Morrigan]

Forgot one thing when the situation is TargetCell1="maximum" and
TargetCell2="value is"

You need to set up a new constraint for TargetCell2:
ie. TargetCell2 = your target value.

 

[Janus]

What an elegant solution. I figured out your missing part just befor
your last note arrived, but would not have found it had you not set m
on the right track. Have spent the afternoon exploring all th
possibilities. Many thanks, Morrigan. Janu

 

[Tushar Mehta]

You can save a Solver model and load another model whenever you want.
Set up one model. Then, in the Solver dialog box click the Options
button. In that dialog box, click Save Model... button and save the
model in some range.

Clear the model in the Solver dialog box and set up the next model.
Once done, save this in a separate worksheet range.

You can have as many models as you want. When I used Solver heavily,
there were times I would have several optimization models stashed away
on the same worksheet.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Janus]

Thanks, Tashur. Your approach also works well but we should add one
more step. After saving the model in a range, when wanting to use that
particular model, click on Load Model in the Solver box, and highlight
the cells where the model is saved. Then just OK the rest of the way.
Janus