Regression Using Trendline

To add a trendline, select the chart by clicking on it, then choose Add Trendline... from the Chart menu. If the chart has several data series, either select the desired data series before choosing Add Trendline... or choose the desired data series from the Based On Series box. 

Choose the Type tab and then choose the appropriate fitting function from the gallery of functional forms. (Depending on the data in the series, the exponential, power or logarithmic choices may not be available.) If you choose the polynomial form you can select the order of the polynomial by using the spinner. If you choose 3, for example. Excel will fit a polynomial of order three 

Now choose the Options tab and check the boxes for Display Equation On Chart and Display R-squared Value On Chart then press OK. Excel displays the trendline on the chart as a heavy solid line and the equation (with the least squares coefficients) and Rp- value as a title on the chart. 

If you want to use the coefficients for calculations, youTl have to copy them from the chart and paste them into worksheet cells. Usually the coefficients as displayed in the chart are not precise enough for calculations, but you can number-format the text to display more figures before copying the coefficients. 

---

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. 

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

This regression of log Kp against the temperature corrected log Pl of the compound could potentially yield useful information on the distribution from the slope, m and the y-intercept, b, of the trendline. If equilibrium was established, m is expected to have a value of near — 1 (Pankow and Bidleman 1992). 

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. 

Fit these data to an appropriate polynomial form. First, plot the data and successively fit a linear, quadratic, cubic, and quartic trendline, noting the value each time when the no longer improves, use the Excel Regression Add-On to find the parameters and statistical indicators. Verify that the polynomial chosen does indeed satisfy the usual statistical criteria. In all cases, the constant term must be zero since the (0, 0) data point is without error. 

---