Problem 5-14 Using trendline

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

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. 

These can be solved directly for a and b and their errors. This is the procedure that is used in standard trendlines. The problem with this procedure is that the random errors only occur in the y variable and the errors are assumed to be equal. In many cases there are unequal errors in both variables. 

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. 

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. 

---