# How to find the correlation coefficient

## A. Colin Cameron, Dept. of Economics, Univ. of Calif. - Davis

This January 2009 help sheet gives information on
• Correlation coefficient.
• Calculation using the Data Analysis Add-in.
• Calculation using the CORREL function.

CORRELATION COEFFICIENT

The correlation coefficient between two series, say x and y, equals

Covariance(x,y) / [Sqrt(Variance(x)) * Sqrt(Variance(y))]

where

• 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 carsdata.xls

• 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

Source: cameron.econ.ucdavis.edu

Category: Forex