Regression Using LINEST

The worksheet function LINEST performs linear regression analysis on a set of x,y data points. (LINEST stands for LINear ESTimation, not LINE STraight.) The general form of the linear equation that can be handled by LINEST is 

Mathematical relationships between the regression parameters are given below (N = number of data points, k = number of regression coefficients to be determined)  

The SE(y) parameter, the standard error of the y estimate, is sometimes referred to as the RMSD (root-mean-square deviation). 

The f-statistic is used to determine whether the proposed relationship is significant (that is, whether y does in fact vary with respect to x). For most relationships observed in chemistry, a relationship will unquestionably exist. If it is necessary to determine whether the variation of y with x is statistically significant, or merely occurs by chance, you can consult a book on statistics. 

The regression coefficients, the standard deviations of the coefficients and R, the coefficient of determination, are the statistical parameters of most interest to chemists. 

---

We have encountered Excel s LINEST as a tool for linear regression. Unfortunately, LINEST cannot be generalised from vectors to matrices. To deal with matrices, we do not have an option but to use equations (4.59) and (4.61). It is possible to do so, but not as convenient as in Matlab. 

The first step is to plot the data (figure 5.10) and then perform a regression analysis of the calibration data and the uncertainty in the calibration coefficients a and b. This is best performed using LINEST as shown in spreadsheet 5.6. LINEST indicates the values of a and b are 0.2274 and l.085mM respectively, with the standard deviations 0.0095 for sa and 0.042mM-1 for sb. 

At this point, a considerable amount of theory on Hansch analysis has been presented with almost no examples of practice. The next three Case Studies will hopefully solidify ideas on Hansch analysis that have already been discussed. Each Case Study introduces a different idea. The first is an example of a very simple Hansch equation with a small data set. The second demonstrates the use of squared parameters in Hansch equations. The third and final Case Study shows how indicator variables are used in QSAR studies. If you are unfamiliar with performing linear regressions, be sure to read Appendix B on performing a regression analysis with the LINEST function in almost any common spreadsheet software. A section in the appendix describes in great detail how to derive Equations 12.20 through 12.22 in the first Case Study. 

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. 

A statistical function of considerable use for chemists is LINEST linear esfimation). It returns the least-squares regression parameters of the linear function that best describes a data set. LINEST is discussed in detail in Chapter 11. 

The data table (Figure 11-7) has been compressed by hiding rows containing some of the less interesting data. To obtain the regression parameters, a 3R x 4C array was selected on the spreadsheet and the formula =LINEST(G5 G37,B5 E37,0,1) was entered. Since an array is to be returned, the array formula was entered by using COMMAND+ENTER (Macintosh) or CONTROL+SHIFT+ENTER (Windows). The array of returned values is shown in Figure 11-8. 

LINEST can also be used to find the regression coefficients for equations of higher order. It is sometimes convenient, in the absence of a suitable equation, to fit data to a power series. The equation can then be used for data interpolation. Often a power series y = a + hx + cx + dx is sufficient to fit data of moderate curvature. The lowest order polynomial that produces a satisfactory fit should be used if there are N data points, the highest order polynomial that can be used is of order (N -1). 

Compare the standard deviations using the forgoing procedure (cells L24 and M24 in Figure 12-9) with those obtained from LI NEST, which are shown in row 32 of Figure 12-10. Once again, it should be made clear that a linear problem was chosen to permit comparison between the standard deviations of the regression coefficients and those from LINEST,... 

Plotting 1/y vs /x will yield a straight line with slope X/ab and intercept X/a. LINEST can be used to provide the regression coefficients X/ab and 1/fl, and their associated standard deviations. The coefficients a and b can be obtained from the regression coefficients a = 1/intercept, b = intercept/slope). However, relationships dealing with the propagation of error must be used to calculate the... 

This command macro returns the standard deviations of regression coefficients obtained by using the Solver, plus the correlation coefficient and the RMSD these statistical parameters are not available from the Solver. The array of values returned is in a format similar to that returned by LINEST. 

Excel has three built-in facilities for least-squares calculations, which provide the same (and, if you wish, much more) information. The first, LINEST, is a simple function. The second is the Regression macro in the Analysis Toolpak, which is part of Excel but must be loaded if this was not already done at the time the software was installed. The third (and often simplest) method is to use the Trendline feature, which is only available once the data appear in a graph. Later we will encounter yet another option, by using the weighted least squares macro described in chapter 10. Truly an embarrassment of riches Below we will illustrate how to use the first three of these tools. Table 2.6-1 lists their main attributes, so that you can make an informed choice of which one of them to use. 

One way to handle a curved calibration line is to fit the line to a power series. A cubic equation (y = a + bx + ex2 + c/x3) is usually sufficient to fit a case such as Figure 21-1. (In any event, since there are only six known points, you couldn t use a polynomial with more than five adjustable parameters.) You can use either LINEST or the Solver to obtain the coefficients of the power series. Figure 21-2 shows a spreadsheet in which LINEST is used to find the regression coefficients for the equation Rdg = a + b x ppm + c X (ppm) + d X (ppm) A... 

The chart (Figure 22-5) is used only to verify that the transformed data fit a linear relationship. LINEST was used to obtain the constants from the slope and intercept of the regression line. 

Oxygen.xls illustrates the use of the LINEST function to perform multiple linear regression. 

We will now introduce some new statistics that allow us to calculate the standard deviation (Sc) of the unknown concentration determined from the calibration curve, based on the number of measurements (N), the slope (m) of the cahbration curve, the average absorbance (yave)> the sum of the squares of deviations (S ) of the individual concentrations (jc,) from the mean of x (jCave)> and the standard deviation about regression (Sr). First review the use of the Excel LINEST statistical functions in Section 3.20. We will use some new syntaxes (statistical functions) to help perform the calculations (review useful syntaxes in Section 3.8). 

A regression analysis was carried out using the plotting wizard and the Linest function associated with an Excel spreadsheet (see Figure 13.2). The linearity of the plot indicates that the data are consistent with the assumed rate expression and that... 

Use of modem spreadsheets such as Excel facilitates the solution of problems of this type, preparation of appropriate plots of the data for visualization, and regression analyses to generate model parameters and the nncertainties associated therewith. The parameter (Cgo - iCpj )k was obtained using the linear trendline and Linest capabilities of Excel. 

Alternatively, any spreadsheet can be used to determine constants A and B.The [REGR] function in a spreadsheet like ExceF or Lotus 1-2-3 is used. The [REGR] function is defined as (=LINEST(known y s,known x s,TRUE,TRUE). To use this function, you must first put the FPY function into the form y = Ax -PB.This is done by creating two columns complexity index (which we will call XI) and yield. A third column is created for log[log(Xl)]), whereas a fourth column is created for log[ln(-yield/100)]). Provide the regression function with column 4 as known ys and column 3 as known xs. The regression function will return 10 values FIT (slope int.), sig-M (slope int.), r2, sig-B(slope int.), F,df (slope int.), and reg sum sq (slope int.). The constant B is equal to the FIT (slope) and the constant A is [-FIT(int.)/FIT(slope)]. (Remember, to calculate an array, follow these steps highlight the array on the spreadsheet type the array formula, making sure that the cursor is in the edit bar then press CTRL -t SHIFT -t ENTER.)... 

---