Excel trendline

The parameters are easily determined by using computer software. In Microsoft Excel, the data are put into columns A and B and the graph is created as for a linear curve fit. This time, though, when adding the trendline, choose the polynomial icon and use 2 (which gives powers up to and including x ). The result is... 

Rather than writing a short program in Matlab for this result, we demonstrate how to perform the task of a straight line fit in Excel. Excel actually provides several ways of performing the job of fitting the best line through a set of data pairs. The most convenient is probably the Add Trendline. .. tool which delivers the result in a few clicks. 

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. 

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. 

Now that everyone has a powerful computer in their back-pack or on their desk, it is simple to fit a straight or even a curved line to data using the TrendLine feature of Excel. As an example, let us work on some real data for DDT in trout from Lake Michigan. 

Figure 2.3 Graph created with Microsoft Excel showing the natural logarithms of the concentrations of DDT in trout from Lake Michigan (see the above table) as a function of time and showing a fitted straight line (using the TrendLine feature). The negative slope of this line is the rate constant.

Strategy. Let us just plot the raw data in Excel and use the TrendLine feature to fit an exponential line. Be sure to turn on the optional show equation and correlation coefficients feature. We get the plot shown in Figure 2.5. 

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. 

Plotting o Graph of the Data and the Least-Squares Fit It is customary and useful to plot a graph of the data and the least-squares fitted line similar to Figure 8-9. The built-in Chart Wizard of Excel makes creating such plots relatively easy. There are several ways to display the data points and the predicted line simultaneously. One way is to plot the predicted values y and the experimental y values simultaneously. The predicted values are given in Table 8-2. The easiest way is to have Excel add the line, called a TrendLine, itself. 

There is one aspect of Excel that initially may confuse you it is the problem of changing options, i.e., of menu items that appear or disappear depending on prior action on the spreadsheet. While this can greatly enlarge the usefulness of the spreadsheet, it can be quite unsettling to the novice, hence this alert. Below we will illustrate it with Trendline, a very useful feature of Excel (to be described in more detail in chapter 2) that allows you to draw a number of least-squares lines or curves through graphed data. 

The shorthand instruction for using Trendline in Excel 95 might read Insert O Trendline, but if you look in the pull-down menu under Insert you may not find Trendline. The problem is that the Trendline option appears only after you have activated the graph (by double-clicking on an embedded chart, or by clicking on the tab of a separate chart). Even then, it shows but cannot be used for the latter, you must first select the particular data set in the graph to which you want it to apply. Only then can you select Trendline (or, for that matter, Error Bars). 

A similar situation applies to Excel 97. Here, Trendline is part of the Chart menu, and the shorthand instruction might read Chart => Trendline. Butyou will usually not find Chart on the menu bar. It only appears there, instead of Data, after you have activated the chart, and again becomes accessible only afteryouhave selected a particular data set. 

Data analysis modern spreadsheets contain several convenient data-analysis aids, such as Excel s Trendline, a flexible linear least-squares tool, and Excel s Solver, a powerful multi-parameter non-linear least-squares fitting routine. Both of these are described in detail in chapter 3, and are used throughout the remainder of this book. Excel also contains a large number of tools for statistical data analysis. 

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

The Excel spreadsheet will carry out fits on a graph with the intercept required to have a specific value. After finishing the graph and selecting Trendline, click on the Options tab, and then click on the Set intercept box and specify 0 or another appropriate value for the intercept. You should include the equation for the curve and the square of the correlation coefficient. 

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. 

To find a polynomial that fits the data, first highlight the x-y data. Then go to the Excel tool bar and click on Layout Analysis Trendline More Trendline Options. Choose polynomial as type and in the menu select the desired order of the polynomial. (You can try different orders to find which has the best fit.) Make sure the boxes Display Equation on Chart and Set intercept = 0 are checked. Then click Close. 

Another way of getting the coefficients for a limited number of fitting functions is to use X-Y scatter graphs in Excel and add a trendline. 

The cs are identical to those found by Excel when a trendline was added to the plot of the data. The cs were determined using the inverse of the matrix C. The C matrix and its inverse have other powerful uses as well, as will be covered in later discussions. 

This equation together with the correlation coefficient can be found using Excel s graphing capabilities. Simply plot the data (points only) and add a trendline (Figure 7.5). Use the appropriate Option to show the equation and R. The coefficients for the quadratic trendline are identical to those calculated previously. 

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. 

---