Page 347 - Excel for Scientists and Engineers: Numerical Methods

3 24 EXCEL: NUMERICAL METHODS

For ease of editing an extensive series of references in the By Changing Cells
input box, press F2; you can then use the arrow keys to move within the box.
Constraints. With the Solver you can apply constraints to the solution. For
example, you can specify that a parameter must be greater than or equal to zero,
or that a parameter must be an integer. Although the ability to apply constraints
to a solution may be tempting, it can sometimes lead to an incorrect solution.
Don't introduce constraints (e.g., to force a parameter to be greater than or equal
to zero) if you're using the Solver to obtain the least-squares best fit. The
solution may not be the "global minimum" of the error-square sum, and the
regression coefficients may be seriously in error.
Add, Change, Delete. The Add, Change and Delete buttons are used to
apply constraints to the model. Since the use of constraints is to be avoided,
these buttons are not of much interest.
Guess. Pressing the Guess button will enter references to all cells that are
precedents of the target cell. In the example in Figure 14-9, pressing the Guess
button enters the cell references $A$IO:$B$25, $B$7, $B$5, $E$6:$E$7 (t values,
E-B, C-A, k-I, k-2, respectively) in the By Changing Cells box. Obviously,
some of these coefficients must not be allowed to vary. Avoid using the Guess
button.
Reset All. The current Solver model is automatically saved with the
worksheet. The Reset All button permits you to "erase" the current model and
begin again.

Some Notes on the Solver Options Dialog Box
The Options button in the Solver Parameters dialog box displays the Solver
Options dialog box (Figure 14-7) and allows you to control the way Solver
attempts to reach a solution. The default values of the options are shown in
Figure 14-7.
Max Time and Iterations. The Max Time and Iterations parameters
determine when the Solver will return a solution or halt. If either Max Time or
Iterations is exceeded before a solution has been reached, the Solver will pause
and ask if you want to continue. For most simple problems, the default limits
will not be exceeded. In any event, you don't need to adjust Max Time or
Iterations, since if either parameter is exceeded, the Solver will pause and issue a
Tontinue anyway?" message.
Precision and Tolerance. Both the Precision and Tolerance options apply
only to problems with constraints. The Precision parameter determines the
amount by which a constraint can be violated. The Tolerance parameter is
similar to the Precision parameter, but applies only to problems with integer
solutions. Since adding constraints to a model that involves minimization of the