Page 349 - Excel for Scientists and Engineers: Numerical Methods

326 EXCEL: NUMERICAL METHODS

worksheet, so be sure to display enough decimal places on the worksheet so that
you'll be able to see the progress of the iterations.) Also, for a large model that
takes a long time to calculate, you can press ESC at any time to halt the iteration
process and inspect the current results, and then continue.
Estimates, Derivatives and Search. These coefficients can be changed
to optimize the solution process. The Search parameter specifies which gradient
search method to use: the Newton method requires more memory but fewer
iterations, while the Conjugate method requires less memory but more iterations.
The Derivatives parameter specifies how the gradients for the search are
calculated: the Central derivatives method requires more calculations (and will
therefore be slower) but may be helpful if the Solver reports that it is unable to
find a solution. The Estimates parameter determines the method by which new
estimates of the coefficients are obtained from previous values; the Quadratic
method may improve results if the system is highly nonlinear. For the majority
of problems, you probably will not detect any difference in performance with any
of these options.
Save Model... and Load Model .... The current Solver model is
automatically saved with the worksheet. The Save Model.. . and Load Model.. .
buttons permit you to save multiple Solver models. An additional 512 bytes are
added to the workbook for each model that is saved.

When to Use Manual Scaling
The Use Automatic Scaling option is important for many problems, but so is
manual scaling. Even when Use Automatic Scaling is in effect, the Solver may
still be unable to find a solution. Automatic Scaling rescales the model based on
values at the initial point. Objective and changing cells are scaled so their scaled
values at the initial point are 1. But, if a value is less than 1E-05 at the initial
point, that value is not scaled. Thus, even though you have checked the Use
Automatic Scaling box, scaling may not be in effect. Therefore, you need to be
aware of the need for manual scaling.
To apply manual scaling to the changing cells, modify one or more formulas
so that the changing cells are all within three orders of magnitude or less of each
other. For example, in the NMR titration example described in the previous
paragraph, you could re-formulate the calculation so as to use log K instead of K.
(Note that you can't apply a scaling factor directly to a changing cell, since it
must be a number value that can be changed by the Solver; the scale factor must
be incorporated into the target cell formula or into one of the intermediate
formulas.)
In my experience, if the magnitude of the objective (the target cell) is very
small (e.g., 1E-09), the Solver may assume that convergence has been reached