Microsoft Office Tutorials and References
In Depth Information
Logarithmic trendline
Exponential: Used when data values rise or fall at increasingly higher
rates. The data cannot contain zero or negative values.
Polynomial: Used when data fluctuates in an orderly pattern. You can
specify the order of the polynomial (from 2 to 6) depending on the
number of fluctuations in the data.
The Type tab in the 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. Moving averages are discussed later in this
chapter.
Earlier in this chapter, I noted that the equation for a straight line uses the slope
and y-intercept. Nonlinear trendlines also have equations, but these equations are
more complex. The following sections cover the nonlinear trendlines available in
Excel, and I provide the equations for each type.
Logarithmic trendline
A logarithmic trendline may be appropriate for data that follows a logarithmic
curve: It increases or decreases quickly and then levels out. A logarithmic trendline
appears as a straight line on a chart with a linear y-axis scale and a logarithmic
x-axis scale. The equation for a logarithmic trendline is
y = (c * LN(x)) - b
Figure 5-11 shows a chart with a logarithmic trendline added. The formula in
cell E2, which follows, calculates c.:
=INDEX(LINEST(B2:B11,LN(A2:A11)),1)
The formula to calculate b, in cell F2, is
=INDEX(LINEST(B2:B11,LN(A2:A11)),2)
Column C shows the predicted y values for each value of x, using the calculated
values for b and c. For example, the formula in cell C2 is
=(\$E\$2*LN(A2))+\$F\$2
Search JabSto ::

Custom Search