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:
The value of R-squared calculated by the RSQ function is valid only for a linear trend-
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
• 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.