Macro using SolvStat

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. 

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... 

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. 

To use the macro, simply Open SolvStat.xls it will appear on screen and then Hide itself. It installs a new menu command. Solver Statistics..., immediately under the Solver... command in the Tools menu. If the Solver Addin has not been loaded, the Solver Statistics... command will be at the top of the menu. The command will remain in the menu until you exit from Excel. 

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. ... 

---