Microsoft Office Tutorials and References

In Depth Information

**Working with Trendlines**

The R-squared value is 0.67.

What do these numbers mean? You can describe a straight line with an equation of the form:

y = mx +b

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 example, 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 at or near 1.

Calculating the slope and
y-intercept

As you know, Excel can display the equation for the trendline in a chart. This equation shows the

slope
(m)
and
y-intercept
(b)
of the best-fit trendline. You can calculate the value of the slope

and
y-intercept yourself, using the LINEST function in a formula.

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

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

The formula that follows is a multicell array formula that displays its result (the slope and
y-inter-

cept) in two cells:

{=LINEST(C2:C11,B2:B11)}