Excel Worksheet Functions for Correlation
Excel provides two worksheet functions for calculating correlation — and, they do exactly the same thing in exactly the same way! Why Excel offers both
PEARSON is unclear, but there you have it. Those are the two main correlation functions.
The others are
RSQ calculates the coefficient of determination (the square of the correlation coefficient).
COVARIANCE.P determines covariance. It uses N-1.
COVARIANCE.S uses N.
CORREL and PEARSON
Here, you see an example of
CORREL along with the Function Arguments dialog box for
To use this function, follow these steps:
- Type the data into cell arrays and select a cell for
Enter the SAT data into C3:C22 and the GPA data into D3:D22, and selected F15. SAT has been defined as the name of C3:C22 and GPA as the name of D3:D22.
- From the Statistical Functions menu, select CORREL to open its Function Arguments dialog box.
- In the Function Arguments dialog box, enter the appropriate values for the arguments.
In the Array1 box, enter
SAT— the name assigned to the cell range (C3:C22) that holds the scores for one of the variables.
In the Array2 box, enter
GPA— the name assigned to the cell range (D3:D22) that holds the scores for the other variable.
With values entered for each argument, the answer, 0.81662505, appears in the dialog box.
- Click OK to put the answer into the selected cell.
CORRELgives you exactly the same answer, and you use it exactly the same way.
If you have to quickly calculate the coefficient of determination (r2),
RSQ is the function for you. There really is no particular need for this function because it’s easy enough to use
CORREL and then square the answer.
Here’s what the Excel Formula bar looks like after you fill in the RSQ Function Arguments dialog box for this example:
In terms of the dialog box, the only difference between this one and
PEARSON) is that the boxes you fill in are called Known_y’s and Known_x’s rather than Array1 and Array2.
COVARIANCE.P and COVARIANCE.S
As far as calculating correlations go, there is really no need for these formulas.
You use these functions the same way you use
CORREL. After you fill in the Function Arguments dialog box for
COVARIANCE.P for this example, the formula in the Formula bar is
If you want to use this function to calculate r, you divide the answer by the product of
STDEV.P(GPA). It’s up to you to figure out how you’d use
COVARIANCE.S to calculate r. It’s unclear why you’d bother with all of this when you can just use