## 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.xIa versions are provided on the CD.

The macro calculates the dFn/daj 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 (dF/da,)(dF/daj) are computed for each of the N data points and the Z,(dF/da,)(dF/dcij) terms obtained. The Py matrix of ~L(dFldal)(dFldaJ) 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 _v0bsd data, (ii) the yca]c 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.

Select range of know y's,

Figure 14-10. Step 1 of 4 of the Solver Statistics macro

The macro calculates the partial derivatives of the function, creates a matrix of sums of cross products, inverts the matrix and uses the diagonal elements to calculate the standard deviations.

If the SolvStat macro is used with the kinetics data of Figure 14-9, the regression coefficients shown in Figure 14-11 are returned. The array of values returned is in a format similar to that returned by LINEST: the regression coefficients are in row 5, the standard errors of the coefficients are in row 6 and the R2 and SE(y) or RMSD parameter are in row 7.

H ! |
J | ||

5 |
0.6331972 |
0.2856394 |
2528 05913 |

S 7 |
0 0477662 0.9972227 |
0.0191613 0.0003983 |
138.954138 |

Figure 14-11. Regression statistics returned by the SolvStat macro.

Figure 14-11. Regression statistics returned by the SolvStat macro.

The regression coefficients in row 5 are not calculated by the macro, but are the values returned by the Solver; they are provided simply to indicate which standard deviation is associated with which coefficient, since the Solver coefficients can be in nonadjacent cells.

## Post a comment