Page 351 - Excel for Scientists and Engineers: Numerical Methods

328 EXCEL: NUMERICAL METHODS

dFn/aaj is the partial derivative of the function with respect to ai evaluated at
xn. The above expressions can be found in some texts on nonlinear regression*.
SEb) is as defined in equation 13-19.
It's possible to carry out these calculations using a spreadsheet, but it's
laborious and error-prone. A macro to perform the calculations is provided on
the CD that accompanies this book.

The Solver Statistics Macro

The SolvStat Add-In returns regression statistics for regression coefficients
obtained by using the Solver. The values returned are the standard deviations of
the regression coefficients, plus the R2 and SE(y) statistics
The add-in installs a new menu command, Solver Statistics ..., in the Tools
menu. If the Solver add-in has been loaded, the Solver Statistics... command
will appear directly under the Solver ... command in the Tools menu; if Solver is
not installed, the Solver Statistics ... command will appear at the bottom of the
menu. See "Loading the Solver Add-In" earlier in this chapter for instruction on
how to load the add-in. Both SolvStat.xls and SolvStat.xla versions are provided
on the CD.
The macro calculates the aFn/i%i terms for each data point by numerical
differentiation, in the same way as in Chapter 6 (see the worksheet "Derivs by
Sub Procedure"). This process is repeated for each of the k regression
coefficients. Then the cross-products (~F/~u,)(~F/au,) are computed for each of
the N data points and the Z(~F/au,)(~F/~u,) terms obtained. The P, matrix of
Z(aF/au,)(aF/au,) terms is constructed and inverted. The terms along the main
diagonal of the inverse matrix are then used with equation 14-5 to calculate the
standard deviations of the coefficients. This method may be applied to either
linear or nonlinear systems.
When you choose the Solver Statistics ... command, a sequence of four
dialog boxes will be displayed, and you will be asked to select four cell ranges:
(i) the yobsd data, (ii) the ycalc data, (iii) the regression coefficients obtained by
using the Solver and (iv) a 3R x nC range of cells to receive the statistical
parameters. The Step 1 dialog box is shown in Figure 14-10. The yobsd and ycalc
values can be in row or column format. The Solver coefficients can be in non-
adjacent cells.

* For example, K. J. Johnson, Numerical Methods in Chemistry; Marcel Dekker, Inc.,
New York, 1980, p. 278.