EXCEL 97 Two-Variable: Correlation

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

This September 1999 help sheet gives information on how to obtain between two or more data series.
 

INPUT DATA

The data used are in cars.xls (Excel Basics: Sample Excel Worksheet).

The spreadsheet cells A1:B6 should look like:
 
cars  hh size
   1    1
   2    2
   2    3
   2    4
   3    5

CORRELATION COEFFICIENT

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

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

where

Using Tools | Data Analysis The output is
 
     CARS HH SIZE
   CARS         1 
   HH SIZE    0.894427      1

The correlation coefficient is 0.894427.

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:B6 and produces a 5 x 5 table of correlations.

Using the correlation command

Alternatively directly type  = CORREL(A1:A6,B1:B6)  which yields  0.894427.
 

COVARIANCE

This is obtained in a similar way to correlation.

Using Tools | Data Analysis | Covariance yields
 
     CARS HH SIZE
   CARS       0.4   
   HH SIZE       0.8       2

Using the covariance commands  = CORREL(A1:A6,B1:B6)  yields  0.8.
 

For further information on how to use Excel go to
     http://www.econ.ucdavis.edu/faculty/cameron