Microsoft Office Tutorials and References

In Depth Information

**Working with Trendlines**

Working with nonlinear trendlines

Besides linear trendlines, an Excel chart can display trendlines of the following types:

h
Logarithmic:
Used when the rate of change in the data increases or decreases quickly,

and then flattens out.

h
Power:
Used when the data consists of measurements that increase at a specific rate. The

data cannot contain zero or negative values.

h
Exponential:
Used when data values rise or fall at increasingly higher rates. The data

cannot contain zero or negative values.

h
Polynomial:
Used when data fluctuates. You can specify the order of the polynomial

(from 2 to 6) depending on the number of fluctuations in the data.

The Trendline Options tab in the Format Trendline dialog box offers the option of

Moving Average, which really isn’t a trendline. This option, however, can be useful for

smoothing out “noisy” data. The Moving Average option enables you to specify the

number of data points to include in each average. For example, if you select 5, Excel

averages every group of five data points, and displays the points on a trendline.

Earlier in this chapter, I describe how to calculate the slope and
y-intercept for the linear

equation that describes a linear trendline. Nonlinear trendlines also have equations, as described in the

sections that follow.

The companion CD-ROM contains a workbook with the nonlinear trendline examples

described in this section. The file is named
nonlinear trendlines.xlsx
.

Logarithmic trendline

The equation for a logarithmic trendline is as follows:

y = (c * LN(x)) – b

Figure 17-27 shows a chart with a logarithmic trendline added. A single array formula in E2:F2

calculates the values for
c and
The formula is
b.

{=LINEST(B2:B11,LN(A2:A11))}