Microsoft Office Tutorials and References
In Depth Information
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