Microsoft Office Tutorials and References
In Depth Information
to 1. Numbers close to 1 mean that the regression line does a great job of pre-
dicting the values. Numbers close to 0 mean that the regression result can ’ t
predict the values at all.
r-squared is the statistic in the third row, first column of a LINEST func-
tion. It is also the square of the PEARSON function. You could use
=INDEX(LINEST(),3,1) or =PEARSON()^2. But instead, Excel provides the
easy-to-remember RSQ function.
The RSQ function returns the square of the Pearson product – moment cor-
relation coefficient through data points in known_y'sand known_x's. The r-
squared value can be interpreted as the proportion of the variance in ythat
is attributable to the variance in x.
The RSQ function takes the following arguments:
known_y's — This is an array or a range of data points.
known_x's — This is an array or a range of data points.
The arguments must be either numbers or names, arrays, or references that
contain numbers. If an array or a reference argument contains text, logical
values, or empty cells, those values are ignored; however, cells that con-
tain the value 0 are included. If known_y'sand known_x'sare empty or have
a different number of data points, RSQ returns an #N/A error.
Figure 14.21 shows four data sets and their associated r-squared values:
• The chart in the top-left corner has an r-squared near 0. There is
little predictive ability in this regression line. In fact, the regression
line is practically a horizontal line drawn through the mean of the