Page 350 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 14 NONLINEAR REGRESSION USING THE SOLVER 327

and may not attempt to improve the solution'. Since many scientific problems
can have values of the objective that are very small, manual scaling of the
objective is extremely important. According to FrontLine Systems, "The user
should always be cautious when thejnal objective function is small and very
cautious when the objectionjimction is less than 1E-5 in absolute value. The
best way to avoid scaling problems is to carefully choose the 'units' used in your
model so that changing cells and target cell are all within a few orders of
magnitude of each other, andpreferably not less than 1 in absolute value."
You can apply a scale factor directly to the objective function. For example,
an objective function formula such as
=SUM( D4: D22)
that yields a sum-of-squares result with order of magnitude 1 E-9 can simply be
changed to the formula
=I EOS*SUM(D4:D22)
If you apply a scale factor to the objective, be sure to examine the objective
after minimization. You may need to increase the magnitude of the scale factor
and rerun the Solver.

Statistics of Nonlinear Regression

The only problem with the use of the Solver to perform least-squares
regression is that, although you get the regression coefficients readily, the results
aren't much use if you don't know their uncertainties as well. These aren't
available from the Solver. The following illustrates how to obtain the standard
deviations of the regression coefficients after obtaining the coefficients by using
the Solver.
The standard deviation of the regression parameter ai is given by equation
14-5.

6 = 4pii-' SECy) ( 14-5)
where Pii-l is the ith diagonal element of the inverse of the Pij matrix

(1 4-6)

~~
* This can sometimes result in a situation where good initial estimates, which result in a
very small value of the objective, do not lead to a solution, while for the same model,
poorer initial estimates give a solution.