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

Similar articles: