LINEST function

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. 

Excel has an alternative way of doing the same. The column of l s can be omitted, selecting only the three others and fitting with the LINEST function but having the Const option set to TRUE. Excel internally adds the columns of ones and delivers exactly the same results. That one special parameter is the z/-intercept. Similar options exist in the trendline function. 

All these statistics are available in Excel in the LINEST function. This gives an array output, and for fitting data to a straight line (equation 2.29) is created by following these steps ... 

The Excel function LINEST returns the slope and intercept and their uncertainties in a table (a matrix). As an example, enter x and y values in columns A and B. Then highlight the 3-row X 2-column region E3 F5 with your mouse. This block of cells is selected to contain the output of the LINEST function. Under the insert menu, select function. In the window... 

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. 

The LINEST function requires four arguments, the locations of the cells that contain the activity (y-data), the cells with the parameter values (x-data), a boolean variable for requesting calculation of a y-intercept, and a boolean variable for requesting statistical output (Figure B.2). The exact syntax of the LINEST function depends on the software package. 

In Excel, the LINEST function uses the following format ... 

In Excel, enter the LINEST function with its correct arguments. Ranges of cell values can be entered by highlighting the cells. 

Using LINEST in OpenOffice is nearly identical to Excel. Columns are filled with data, and the arguments are entered into the LINEST function (Figure B.4). One key difference is that the arguments are separated by a semicolon instead of a comma. Another key difference is that when the function is entered, using Ctrl+Shift+Enter automatically causes LINEST to generate its full output in multiple cells. Additional cells need not be selected as with Excel. The output of LINEST in OpenOffice is identical to that in Excel, and the positions of the data is identical as well. 

Equation 12.21 correlates the Taft steric parameter of the R-group (Es) to the experimental activity of the molecule (log 1 //50). As with the previous section, the appropriate columns of activities and parameter values are created and entered into the LINEST function as... 

Execution of LINEST occurs as usual with one caveat related to Excel. Because an additional column is included in the x-values, an additional column must be selected for the output of the LINEST function. In Excel, the selected area would need to be five cells tall and three cells wide to hold the full output. In OpenOffice and Google Docs, allocating space for the output of LINEST is handled automatically without help from the user. The full output is shown to Figure B.8. The cells containing N/A can be disregarded. 

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. 

If the LINEST argument constjogicalis set to FALSE, the m coefficient of the line of the form y = mx that best fits the data is returned. Applying the LINEST function to the permanganate data yields the parameters shown in Figure 11-6. 

Now we see how the LINEST function can accomplish many important functions in a single procedure. Begin by using the mouse to select an array of cells two cells wide and five cells high, such as E2 F6. Then click on the Insert Function icon, select STATISTICAL and LINEST in the left and right windows, respectively, and click on OK. Select the Known y s and Known x s as before, then click on the box labeled Const and type true. Also type true in the box labeled Stats. When you click on each of the latter two boxes, notice that a description of the meaning of these logical variables appears below the box. To activate the LINEST function, you must now type the rather unusual keystroke combination Ctrl-1-Shift+ [J]. This keystroke combination must be used whenever you perform a function on an array of cells. The worksheet should now appear as follows. 

Compare your results with those obtained with the LINEST function. We will here use it in its full form, which will require that you highlight an otherwise unusedblock of cells, 2 cells wide and 5 cells high, starting with the top-left cell. 

Also compare your results with those from the Regression routine you can find under Tools => Data Analysis => Regression. This routine provides even more statistical information than the LINEST function, and labels it, but takes more time to execute and does not automatically update itself when the input data are changed. Regression also can make graphs. 

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. 

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

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

---