Regression Using Excel

The last example shows how to fit a polynomial to data. The same thing can be done when the functions are not simple powers, but are more complicated functions. However, to keep the problem linear, the unknown coefficients must be coefficients of those functions that is, the functions are completely specified. Multiple regression simply determines how much of each one is needed. Thus, the form of the equation is 

In Excel, you put the x values in a column and create additional columns, with each column being a function, evaluated for the x value in that row. The example used here is to find the constants in a reaction rate formula. The expected expression is 

Step 1 Obtain columns D, E, and F by taking the logaiithm of columns A, B, and C, respectively. Do this in the first cell (D2), copy it across the E and F rows, and copy down the three rows (D2, E2, and F2). 

Step 2 Proceed with parameter estimation by choosing Tools/Data Analysis, and then 

TABLE E.3. Reaction Rate Data as a Function of Partial Pressures 

---

For more information on nonlinear regression using Excel see. R. Crouch and F. J, Holler, Applications of Microsoft Excel in Analytical Chemistry. 13. Belmont. CA Brks/Cole. 2004. 

LINEST is a function that is included in almost every spreadsheet software, including Microsoft Excel, OpenOffice.org Calc, and Google Docs Spreadsheet. LINEST accepts a table of values for a dependent variable (experimental activity) and any number of independent variables (such as parameters for use in a Hansch equation). LINEST then outputs the best-fit coefficients for the independent variables and certain statistical parameters for the regression. While Excel s Regression option in the Data Analysis tool is more user friendly, LINEST is much more widely available. 

Linear regression using the Excel Data Analysis Add-in... 

It is clear that by adjusting the parameters of this model by nonlinear regression, an excellent fit to the experimental data could be obtained. The value of such a procedure is rather dubious, however, and it is more useful to use the model to obtain qualitative information about the quantities Perf(°°), Peaf(°°) and Big, which are poorly determined in the literature. 

In the present model the pre-exponential factors and activation energies are optimised by non-linear regression using the Solver module on Microsoft Excel. The amounts of coke associated with CO2 evolution at Pi and P2 are also optimised, while a mass balance determines the coke quantity associated with CO at P5 and CO2 at P3. The oxygen partial... 

T Spreadsheet Summary In the final three exercises in Chapter 7 of —I Applications of Microsoft Excel in Analytical Chemistry, we first use Excel to plot a simple distribution of species diagram (a plot) for a weak acid. Then, the first and second derivatives of the titration curve are plotted to better determine the titration end point. A combination plot is produced that simultaneously displays the pH versus volume curve and the second-derivative curve. Finally, a Gran plot is explored for locating the end point by a linear regression procedure. 

Linear analysis was carried out using Excel and the non linear regression analysis was carried out with NLREG (Version 5.2 Phillip H. Sherrod 1991-2001). 

We can use the data in Table E3-1.1 to determine the activation energy, E, and frequency factor. A, in two dififerem ways. One stay is to make a semilog plot of k vs. iUT) and determine E from the slope. Another way is to use Excel or Polymath to regress the data. The data in Table E3-1.1 was entered in Excel and is. show n in Figure E3-1.1 w hich was then used to obtain Figure E3-I.2. 

We can find a and P from either a semilog plot as shown in Figure E 14-4.2 or by regression using Polymath. MATLAB. or Excel. 

The availability of spreadsheets makes it unnecessary to plot data on graph paper and do hand calculations for the least-squares regression analysis and statistics. We will use the data in Example 3.21 to prepare the plot shown in Figure 3.8, using Excel. 

Prepare the Calibration Curve. It is preferable to measure each standard three times and plot the average the standard deviation of each point can be calculated, and the range of one standard deviation or the range of data for each point can be marked on the calibration curve. Using Excel, plot the trendline, with the regression line equation (slope and intercept). 

Equations (2.12) and (2.13) enable the best-fit calibration line to be drawn through the experimental x,y points. Once the slope m and the y intercept b for the least squares regression line are obtained, the calibration line can be drawn by any one of several graphical techniques. A commonly used technique is to use Excel to graphically present the calibration curve. These equations can also be incorporated into computer programs that allow rapid computation. 

Include all calibration data, ICVs, and sample unknowns for both instrumental methods. Perform a statistical evaluation in a manner that is similar to previous experiments. Use EXCEL or LSQUARES (refer to Appendix C) or other computer programs to conduct a least squares regression analysis of the calibration data. Calculate the accuracy (expressed as a percent relative error for the ICV) and the precision (relative standard deviation for the ICV) from both instrumental methods. Calculate the percent recovery for the matrix spike and matrix spike duplicate. Report on the concentration of Cr in the unknown soil samples. Be aware of all dflution factors and concentrations as you perform calculations ... 

Another interesting item worth commenting on is the significant difference between the Weibull modulus my for the three-point flexure bar rupture data (my = 11.96, oey = 612.7 MPa) and that of the thermal shocked disk (my = 6.91, (j0y = 345.9 MPa) experimental rupture data (shown in Figure 5) as determined by CAKES/Life maximum likelihood parameter estimation. Least squares regression (using an Excel spreadsheet) on the CARES/Life disk predictions... 

---