Solver

[Janus]

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

[Morrigan]

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

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

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

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

You can figure out the other combinations.


Hope it helps.

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

[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.

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

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

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

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

You can figure out the other combinations.


Hope it helps.

[Janus]

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

[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

In article <[email protected]>,
[email protected] says...
>
> Is it possible to have two separate Solver solutions operating in a one
> page workbook in Excel 2003? I have two sets of data on one
> spreadsheet page both require the use of Solver. The data sets are
> independant of each other. I can set Solver to work with one set only
> and would like to also use Solver on the other set without creating a
> second worksheet. Thanks in advance, Janus
>
>
> --
> Janus
> ------------------------------------------------------------------------
> Janus's Profile: http://www.excelforum.com/member.php...o&userid=26890
> View this thread: http://www.excelforum.com/showthread...hreadid=401187
>
>

[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