Page 348 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 14 NONLINEAR REGRESSION USING THE SOLVER 325

error-square sum is not recommended, neither the Precision nor the Tolerance
parameter is of use in nonlinear regression analysis.
Convergence. The Convergence parameter corresponds to the Maximum
Change parameter in the Calculations tab of Excel's Options dialog box (see
Chapter 8, Figure 17), but unlike the Maximum Change parameter, which is an
absolute convergence limit, the Solver's Convergence parameter is relative; the
Solver will stop iterating when the relative change in the target cell value is less
than the number in the Convergence box for the last five iterations. Thus you
don't have to scale the convergence limit to fit the problem, as you do when
using Goal Seek.. . .
Assume Linear Model. If the function is linear, checking the Assume
Linear Model box will speed up the solution process. If the Assume Linear
Model option is checked, the Solver performs a linearity test before proceeding;
if the model fails this linearity test, the Solver returns the message "The
conditions for Assume Linear Model are not satisfied."
Assume Non-Negative. Checking this box is equivalent to setting "greater
than or equal to zero" constraints for each of the coefficients.
Use Automatic Scaling. For some models the Solver may refuse to
converge satisfactorily. The Solver may fail to vary one or more changing cells
or vary them by only an insignificant amount. This can occur when there is a
large difference in magnitude between changing cells, for example, if you are
varying two parameters, an equilibrium constant K, with magnitude 1~10'~ and
an NMR chemical shift 6, with magnitude 0.5, to fit data from an NMR
"titration" (chemical shift as a function of pH). In such cases the Use Automatic
Scaling option should be checked. In the example earlier in this chapter, you
were instructed to check the Use Automatic Scaling box because there was a
large difference between the parameters k-1 and k-2 (both on the order of 1) and
the parameter E-B (on the order of lo3). You may find it constructive to re-run
this example using the original estimates (0.5, 0.3 and 3E+03) but with the Use
Automatic Scaling box unchecked. You will find that the Solver varies k-1 and
k-2 but does not appear to change E-B. But if you examine the value of E-B you
will see that the value did change a very small amount. (When I ran this model,
the value changed from 3000 to 2999.99999714051 .)
Show Iteration Results. If the Show Iteration Results box is checked, the
Solver will pause and display the result after each iteration. You may find it
interesting to try this option when you are first learning to use the Solver.
If you create a model with a large number of cells to recalculate at each
iteration, you may be able to observe the progress of the Solver in another way:
after each iteration, the iteration number and the value of the target cell are
displayed in the Status Bar at the bottom of the Excel worksheet. (The number
format of the target cell in the Status Bar is the same as its format on the