Microsoft Office Tutorials and References

In Depth Information

**Other Solver options**

Linear models

A linear optimization problem is one in which the value of the target cell is a linear function

of each variable cell; that is, if you plot X Y (scatter) charts of the target cell’s value against

all meaningful values of each variable cell, your charts are straight lines. If some of your

plots produce curves instead of straight lines, the problem is nonlinear.

In the Solver Parameters dialog box, you can use the Simplex LP option in the Select A

Solving Method drop-down list only for what-if models in which all the relationships are linear.

Models that use simple addition and subtraction and worksheet functions such as SUM are

linear in nature. However, most models are nonlinear. They are generated by multiplying

changing cells by other changing cells, by using exponentiation or growth factors, or by

using nonlinear worksheet functions, such as PMT.

Linear problems can be solved more quickly by using the Simplex LP option. However, if

you select this option for a nonlinear problem and then try to solve the problem, the Solver

Results dialog box displays an error message. If you are not sure about the nature of your

model, it’s best not to use this option.

The importance of using appropriate starting values

If your problem is nonlinear, you must be aware of one important detail: Your choice

of starting values can affect the solution generated by the Solver. With nonlinear

problems, you should always do the following:

●
Set your variable cells to reasonable approximations of their optimal values

before running the problem.

●
Test alternative starting values to see what impact, if any, they have on the Solver

solution.

Viewing iteration results

If you’re interested in exploring many combinations of your variable cells, rather than only

the combination that produces the optimal result, click Options in the Solver Parameters

dialog box and select the Show Iteration Results check box. When you do, the Show Trial

Solution dialog box appears after each iteration, which you use to save the scenario and

then either stop the trial or continue with the next iteration.

Be aware that if you use Show Iteration Results, the Solver pauses for solutions that do not

meet all your constraints, as well as for suboptimal solutions that do.