Microsoft Office Tutorials and References
In Depth Information
Other Solver options
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
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.