TrendLine feature

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. 

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. 

In order to use the Trendline feature, you need to have the data in graphical form. Fortunately you already made such a graph for instruction (21). 

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. 

Of the synthetic sequences depicted as complexity trendlines in Scheme 8.8, sequence (3) appears to be the most attractive one, as it features a low overall complexity with a late-stage, rapid increase in complexity. In sequence (2), the complexity rises much too quickly—affording high overall complexity. Finally, sequence (1) features intermediates with complexity exceeding that of the target hence, it should be given the lowest rank. Such an analysis allows one to pinpoint deficiencies in synthetic sequences, and to determine at which point in a synthetic sequence improvements would be of the greatest consequence [17]. 

---