Microsoft Office Tutorials and References

In Depth Information

**Syntax:**

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.

Syntax:

=RSQ(known_y's,known_x's)

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.

FormoreinformationonthePearsoncoefficient,see

477
.

The RSQ function takes the following arguments:

•
known_y's

known_y's
—
This is an array or a range of data points.

•
known_x's

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

data points.