The workbook shown in Figure 17-21 also appears on the companion
The second chart also uses the options to display the equation and the R-squared
value. In this example, the equation is:
y = 53.194x + 514.93
The R-squared value is 0.6748.
To display more or fewer decimal places in the equation and R-squared
value, select the box and click the Increase Decimal or Decrease Decimal
button on the Formatting toolbar.
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 (in this case, column B), you can calculate the predicted
value of y (the value on the trendline) by using this equation. The variable m repre-
sents the slope of the line and b represents the y-intercept. For example, the month
of February has an x value of 2 and a y value of 743. The predicted value for
February, obtained using the following formula, is 621.318:
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 your 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 equa-
tion shows the slope (m) and y-intercept (b) of the best-fit trendline. You can calcu-
late the value of the slope and y-intercept yourself, using the LINEST function in a
Figure 17-21 shows 10 data points (x values in column B, y values in column C).
Chapter 17: Charting Techniques