Page 346 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 14 NONLINEAR REGRESSION USING THE SOLVER 323

The Solver provides results that are essentially identical to those from
commercial software packages. Any slight differences (usually ca. 0.00 1 YO or
less) arise from the fact that, with all of these programs, the coefficients are
found by a search method; the "final" values will differ depending on the
convergence criteria used in each program. In fact, you would probably obtain
slightly different results using the same program and the same data, if you started
with different initial estimates of the coefficients.

Some Notes on Using the Solver
External References. The target cell and the changing cells must be on the
active sheet. However, your model can involve external references to values in
other worksheets or workbooks.
Discontinuous Functions. Discontinuous functions in your Solver model
may cause problems. They can be either discontinuous mathematical functions
such as TAN, which has a discontinuity at 7d2, or worksheet functions that are
inherently "discontinuous," such as IF, ABS, INT, ROUND, CHOOSE, LOOKUP,
HLOOKUP, or VLOOKUP.
Initial Estimates. Since the Solver operates by a search routine, it will find a
solution most rapidly and efficiently if the initial estimates that you provide are
close to the final values. As mentioned previously, it is often useful to create a
chart of the data that displays both Yobsd and ycalo and then vary the parameters
manually in order to find a good set of initial parameter estimates.
Global Minimum. To ensure that the Solver has found a global minimum
rather than a local minimum, it's a good idea to obtain a solution using different
sets of initial estimates.
"Unable to find a solution" When There Are a Large Number of
Parameters. For a complicated model with a large number of adjustable
coefficients, the Solver may not be able to converge to a reasonable solution. In
such a case, it is sometimes helpful to perform initial Solver runs with subsets of
the coefficients. For example, to fit a UV-visible spectrum with five Gaussian
bands, and thus 15 adjustable coefficients, you could perform initial runs varying
the coefficients for two or three of the bands at a time. When a reasonable fit has
been found for the subsets, perform a final Solver run varying all of the
coefficients.

Some Notes on the Solver Parameters Dialog Box
There are some additional controls in the Solver Parameters dialog box:
By Changing Cells. You can use names instead of cell references for
individual cells or ranges in the By Changing Cells input box.