Spreadsheet LINEST

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. 

In Chapter 4.2.6, Excel Linest, we discuss the LINEST function of Excel which is much more versatile while still covering the best line fit. LINEST delivers the results into the spreadsheet where they can be used for further calculations. Additionally, LINEST supplies a statistical analysis. 

Bill Excel LINEST function. Enter the data from Problem 4-23 in a spreadsheet and use the LINEST function to find the slope and intercept and standard deviations. Use Excel to draw a graph of the data and add a TRENDLINE. 

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. 

Equation 12.22 correlates both the Hammett constant ( -, ) and the Taft steric parameter (Es) of the R-group to the experimental activity of the molecule (log I//50). As with the other examples, the requisite activity and parameter values must be entered into a spreadsheet. The only significant difference in this example is that the Jt-values argument for LINEST must include two columns of data (Figure B.7). One includes the Hammett constant data, and the other is for the Taft steric parameter. 

In conclusion, the LINEST function found in almost all spreadsheet applications is simple to use and can quickly generate Hansch equations from activity data and tabulated parameter values of R-groups. 

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. 

Note that an alternative to inputting the =LINEST function in the command line is to select your 2x5 array of cells for the output, then to use the function option (from the Insert menu, or toolbar icon fx) and select LI NEST. A dialogue box similar to that shown in spreadsheet 5.5 appears and you can enter the ranges for y and x and the constant and stats in the appropriate spaces. You still need to press Ctrl-Shift-Enter to see all the output in the 5x2 array. 

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. 

Plot the calibration data and determine the calibration parameter and associated uncertainties using LINEST. The calibration plot shown in figure 5.11 confirms the linearity of the data. The results table from LINEST for this data is shown in spreadsheet 5.7... 

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 LINEST program of Excel allows us to quickly obtain several statistical functions for a set of data, in particular, the slope and its standard deviation, the intercept and its standard deviation, the coefficient of determination, and the standard error of the estimate, besides others we will not discuss now. Linest will automatically calculate a total of 10 functions in 2 columns of the spreadsheet. 

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

---