You are currently offline, waiting for your internet to reconnect

`PEARSON(array1, array2)`

The most common use of PEARSON includes two ranges of cells that contain the data, such as PEARSON(A1:A100, B1:B100).

- Create a blank Excel worksheet, and then copy the following table.
1 = 3 + 10^$D$2 Power of 10 to add to data 2 =4 + 10^$D$2 0 3 =2 + 10^$D$2 4 =5 + 10^$D$2 5 =4+10^$D$2 6 =7+10^$D$2 pre-Excel 2003 when D2 = 7.5 =PEARSON(A1:A6,B1:B6) 0.702038 =CORREL(A1:A6,B1:B6) 0.713772 when D2 = 8 #DIV/0! 0.713772 - Select cell A1 in your blank Excel worksheet, and then paste the entries so that the table fills cells A1:D13 in your worksheet.
- Click the
**Paste Options**button, and then click**Match Destination Formatting**. With the pasted range still selected, use one of the following procedures, as appropriate for the version of Excel that you are running:- In Microsoft Office Excel 2007, click the
**Home**tab, click**Format**in the**Cells**group, and then click**AutoFit Column Width**. - In Microsoft Office Excel 2003, point to
**Column**on the**Format**menu, and then click**AutoFit Selection**.

**Number**with 0 decimal places. - In Microsoft Office Excel 2007, click the

In versions of Excel that are earlier than Excel 2003, PEARSON may exhibit round-off errors. The behavior of PEARSON has been improved in Excel 2003 and in later versions of Excel. CORREL has always been implemented with the improved procedure that is now used in Excel 2003 and in later versions of Excel. Therefore, if you are using PEARSON for a version of Excel that is earlier than Excel 2003, Microsoft recommends that you use CORREL instead.

In versions of Excel that are earlier than Excel 2003, you can use the worksheet in this article to run an experiment and discover when round-off errors occur. If you add a constant to each of the observations in B1:B6, the value of PEARSON or CORREL should not be affected. If you increase the value in D2, a larger constant is added to B1:B6. If D2 is less than 7, there are no round-off errors that appear in the first six decimal places of PEARSON. Now change the value of D2 to 7.25, 7.5, 7.75, and then 8. Cells D6:D13 of the worksheet show values of PEARSON and CORREL when D2 = 7.5 and when D2 = 8, respectively.

CORREL is still accurate, but round-off errors in PEARSON have become so severe that division by 0 occurs when D2 = 8.

Earlier versions of Excel exhibit incorrect answers in these cases because the effects of round-off errors are more profound with the computational formula used by these versions. Still, the cases in this experiment can be viewed as extreme.

In Excel 2003 and in later versions of Excel, you should see no changes in values of PEARSON if you try the experiment. However, cells D6:D13 show the same round-off errors that you obtain in earlier versions of Excel.

CORREL has the same functionality and has always been implemented with the approach that is used for PEARSON in Excel 2003 and for later versions of Excel. Therefore, CORREL is a better choice for earlier versions of Excel.

In most practical examples, however, you are not likely to notice a difference between the results in Excel 2003 and in later versions of Excel and the results in earlier versions of Excel. Typical data is unlikely to exhibit the kind of unusual behavior that this experiment illustrates. Numeric instability is most likely to appear in earlier versions of Excel when data contains both a high number of significant digits and relatively little variation between data values.

The procedure that finds the sum of squared deviations about a sample mean by finding the sample mean, by computing each squared deviation, and then by summing the squared deviations is more accurate than the alternative procedure. (The alternative procedure is frequently referred to as the "calculator formula," because it is suitable for use by a calculator on a small number of data points.) The alternative procedure consists of the following steps:

- Find the sum of squares of all observations, the sample size, and the sum of all observations.
- Compute the sum of squares of all observations minus ((sum of all observations)^2)/sample size).

A short list of such functions includes the following functions:

- VAR
- VARP
- STDEV
- STDEVP
- DVAR
- DVARP
- DSTDEV
- DSTDEVP
- FORECAST
- SLOPE
- INTERCEPT
- PEARSON
- RSQ
- STEYX

Properties

Article ID: 828129 - Last Review: 09/19/2011 00:04:00 - Revision: 3.0

- Microsoft Office Excel 2007

- kbformula kbexpertisebeginner kbinfo KB828129