Microsoft Office Tutorials and References

In Depth Information

predictions. In other words, you can interpret R-squared as the proportion of the variance in
y
attributable to the

variance in
x.

As described previously, you can instruct Excel to display the R-squared value in the chart. Or you can calculate

it directly in your worksheet using the RSQ function. The following formula calculates R-squared for
x
values

in B2:B11 and
y
values for C2:C11:

=RSQ(B2:B11,C2:C11)

The value of R-squared calculated by the RSQ function is valid only for a linear trend-

line.

Working with nonlinear trendlines

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

•
Logarithmic:
Used when the rate of change in the data increases or decreases quickly, and then flattens out.

•
Power:
Used when the data consists of measurements that increase at a specific rate. The data cannot con-

tain zero or negative values.

•
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. You can specify the order of the polynomial (from 2 to 6) depend-

ing on the number of fluctuations in the data.

The Trendline Options tab of the Format Trendline task pane offers the option of Moving

Average, which really isn't a trendline. This option, however, can be useful for smooth-

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

Figure 17-28 shows charts which depict each of the trendline options.