Spreadsheet Trendline

One difficulty with the Trendline is that the equation only appears graphically. The values for slope and intercept have to be copied manually into the spreadsheet if they are to be used in later calculations. 

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. 

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. 

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. 

The linear least-squares line gives a slope of 0.861 and an intercept of —0.002 (using Options under Chart, Add Trendline, when highlighting the chart or line). Hence, the concentration of the unknown is equal to (0.463 — 0.002)70.861, as given by the formula in the spreadsheet (below). The sample concentration is 0.540 ppm. We will now perform the same calculation without charting the calibration curve, and including the standard deviation of the sample concentration. 

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. 

---