Microsoft Office Tutorials and References

In Depth Information

**Linear Trendlines**

Figure 5-8: A linear trendline has been added to this XY chart.

The R-squared value is 0.6095.

What do these numbers mean? You may remember from algebra classes that a

straight line can be described with an equation of the form:

y = mx +b

The variable m represents the slope of the line and b represents the y-intercept.

The slope of a line is the amount by which the y value changes for a 1-unit change

in x. The y-intercept is the value at which the line crosses the y-axis.

In the preceding example, the y-intercept is –122.78. However, the trend line

appears
to intersect the y-axis at (approximately) the 160 value. This

apparent discrepancy is due to the fact that the scale values for the x-axis does not

begin with 0. If you change the axis scaling and extend the trendline, you’ll

see that it meets the y-axis at the –122.78 value.

For each value of x (in this case, column B), you can calculate the predicted

value of y (the value that falls on the trendline) by using the trendline equation. For

example, Adam has a height (x) of 67 inches and a weight (y) of 154 pounds.

Adam’s
predicted
weight (y), using the following formula, is 164.16:

y = 4.2827 * (67) - 122.78