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