Microsoft Office Tutorials and References

In Depth Information

For each value of
x
(the horizontal axis), you can calculate the predicted value of
y
(the value on the trendline)

by using this equation. The variable
m
represents the slope of the line, and
b
represents the
y
-intercept. For ex-

ample, when
x
is 3 (for March), the predicted value of
y
is 674.47, calculated with this formula:

=(53.19*3)+514.9

The R-squared value, sometimes referred to as the
coefficient of determination,
ranges in value from 0 to 1. This

value indicates how closely the estimated values for the trendline correspond to the actual data. A trendline is

most reliable when its R-squared value is closer to 1.

Calculating the slope and y-intercept

This section describes how to use the LINEST function to calculate the slope (
m
) and
y
-intercept (
b
) of the best-

fit linear trendline.

Figure 17-25 shows ten data points (
x
values in column B, actual
y
values in column C).

Figure 17-25:
Using the LINEST function to calculate slope and
y
-intercept.