Solvstat macro

Figure 12-12. Layout of regression parameters and statistics returned by the SolvStat macro.

Comparison of results from tho Solver end the SOLVSTAT macro vith a commensal wftvare package ... 

If the SolvStat.xls macro is used with the kinetics data of Figure 12-3, the regression parameters shown in Figure 12-13 are returned. The values in rows 42, 44 and 46 can be used to compare the results of the SolvStat macro and a commercial statistical software package. 

AN ADDITIONAL BENEFIT FROM USING THE SOLVSTAT MACRO... 

To obtain a standard deviation to express an experimental error with a confidence area, procedures to obtain standard deviations of parameters of general functions are indispensable. These are, however, quite complex. With the aid of spreadsheet software, the standard deviations can be obtained relatively easily. In this case, a convenient way to obtain the standard deviation is to apply the Solvstat macro to the spreadsheet. Doing so is easy and provides reliable results. First, a spreadsheet to obtain regression coefficients by using Solver must be prepared. Then the Solvstat add-in can be used to elucidate standard deviations of regression coefficients. ... 

The preceding example illustrates the steps in the application of the method. The procedure is cumbersome to apply in practice. The macro SolvStat.xls on the diskette that accompanies this book was written to perform the calculations. It returns the standard deviations of the coefficients, the correlation coefficient and the SE(y) or RMSD it can be applied to linear or non-linear regression. 

The SolvStat.xls macro is an Auto Open macro when you Open the document it will appear on screen and then Hide itself. It installs a new menu command. Solver Statistics..., directly under the Solver... command in the Tools menu. If the Solver Add-In has not been loaded, the Solver Statistics... command will be at the top of the menu. The command will remain in the menu imtil you exit from Excel. 

There is an additional major advantage in using the Solver Add-in and the SolvStat.xls macro, even for functions that are linear or can be rearranged to a linear form. For example, if the function is... 

The standard deviations shown in Figure 22-11 were obtained by using the SolvStat.xls macro described in Chapter 12. 

SolvStat.xls is a command macro that returns the standard deviations for nonlinear regression analysis performed by the Solver. See "Instructions for Using SolvStat" at the end of this appendix. 

---