Excel LINEST

As indicated in Chapter 4.2.1, Straight Line Fit, the Excel function LINEST is a more general function than the rRENDLINE. In addition to allowing the 

Returns statistics that describe a linear trend matching known data points, by fitting a straight line usiig the least squares method. 

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. 

---

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. 

U Acid-base titrations were carried out in a similar manner to those in Section 7-8. Volumes and results are shown in the following table.6 Use the Excel LINEST procedure to find the concentrations of acids A, B, and C and estimate their uncertainty. 

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

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. 

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

Table2.8. Output of the Excel LINES function, =LINEST (y-range, x-range, 1,1)...

Dedicated software is often used to fit the data, but it is also possible to calculate the coefficients easily in Excel. Equations 3.16 and 3.17 are linear in the coefficients of the equation and therefore can be obtained by the usual methods such as LINEST. Create columns for the values of each of the factor codes, then add extra columns for the square of these codes (a, etc.) and their products [ab, etc.). When fitting these with LINEST, chose the whole matrix for the x values and the column containing the responses for y. The LINEST output array is now p columns by five rows, where p is the number of coefficients, including the mean response. 

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

LINEST(B2 B5,A2 A5,TRUE,TRUE) appears once again in the formula line. Now press CONTROL+SHIFT+ENTER on a PC or COMMAND(3 )+RETURN on a Mac. Excel dutifully prints out a matrix in cells E3 F5. Write labels around the block to indicate what is in each cell. The slope and intercept are on the top line. The second line contains sm and sh. Cell F5 contains sv and cell E5 contains a quantity called R2, which is defined in Equation 5-2 and is a measure of the goodness of fit of the data to the line. The closer R2 is to unity, the better the fit. 

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

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. 

FIGURE B.8 Full LINEST output in Excel for Equation 12.22... 

If you type the function name in a worksheet cell and then press CONTROL+SHIFT+A, Excel will paste dummy arguments, called placeholder arguments, and add the closing parenthesis. For example, after entering =LINEST, press CONTROL+SHIFT+A. The function will be completed and will appear as follows ... 

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. 

I have used LINEST in Excel, but only get one value. 

Of greatest use, although not so easy to use, is LI NEST which in the command line of Excel has the form =LINEST(y-range, x-range,... 

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. 

---