How to find the correlation coefficient
A. Colin Cameron, Dept. of Economics, Univ. of Calif. - DavisThis January 2009 help sheet gives information on
- Correlation coefficient.
- Calculation using the Data Analysis Add-in.
- Calculation using the CORREL function.
The correlation coefficient between two series, say x and y, equals
Covariance(x,y) / [Sqrt(Variance(x)) * Sqrt(Variance(y))]
- Covariance(x,y) is the sample covariance between x and y: (1/(n-1)) × Σ i (xi - xbar)(yi - ybar)
- Variance(x) is the sample variance of x: (1/(n-1)) × Σ i (xi - xbar) 2
- Variance(x) is the sample variance of y: (1/(n-1)) × Σ i (yi - ybar) 2
CALCULATION USING THE DATA ANALYSIS ADD-IN
The data used are
- In the Data Group select the Data Analysis Add-in
- Select Correlation
- Fill out the Correlation dialog box as below
Hit enter yields
This can be extended to several series.
For example if there are data in columns A, B, C, D and E then the array chosen is A1:E6 and produces a 5 x 5 table of correlations.
CALCULATION USING THE CORREL FUNCTION
This does not require the Data Analysis Add-in
- Click on the cell you want result to appear on.
- On the Formula Tab select the Function Library group and More Functions and Statistical
- Select Correlation and fill out the dialog box as below