Microsoft Office Tutorials and References

In Depth Information

**Syntax**

Note

I am somewhat jealous that Microsoft has named an obscure function

after fellow Excel consultant Chip Pearson. I am lobbying Mi-

crosoft for the inclusion of a JELEN function, possibly used to

measure the degree of laid-backness caused by the gel in your shoe

insoles. Seriously, Chip Pearson
’
s website is one of the best estab-

lished sources of articles on the Web about Excel. To peruse the art-

icles, visit
www.cpearson.com
.

The Pearson product
–
moment correlation coefficient, named after Karl

Pearson, returns a value from
−
1.0 to +1.0. The calculation could make your

head spin, but the important thing to know is that a PEARSON value closer

to 1 or
−
1 means that a linear relationship exists. A value of 0 indicates no

correlation between the independent and dependent variables.

Syntax

=PEARSON(array1,array2)

The PEARSON function returns the Pearson product
–
moment correlation

coefficient, r, a dimensionless index that ranges from
−
1.0 to 1.0, inclusive,

and reflects the extent of a linear relationship between two data sets.

The PEARSON function takes the following arguments:

•
array1

array1
—
This is a set of independent values.

•
array2

array2
—
This is a set of dependent values.

The arguments must be either numbers or names, array constants, or referen-

ces that contain numbers. If an array or a reference argument contains text,

logical values, or empty cells, those values are ignored; however, cells

that contain the value 0 are included. If array1and array2are empty or

have a different number of data points, PEARSON returns an #N/A error.

The result of PEARSON is also sometimes known as r. Multiplying PEARSON

by itself leads to the more famous r-squared test.

Using

Using
RSQ

to Determine the Strength of a Linear Relationship

r-squared is a popular measure of how well a regression line explains the

variability in the y values. It is popular because the values range from 0

RSQ
to Determine the Strength of a Linear Relationship