• correlation coefficients
• covariances

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

• Covariance(x,y) is the sample covariance between x and y 
  = Sum over i of (x_i - xbar)(y_i - ybar)  divided  by (n-1)
• Variance(x) is the sample variance of x 
  = Sum over i of (x_i - xbar)^2  divided  by (n-1)
• Variance(x) is the sample variance of y 
  = Sum over i of (y_i - ybar)^2  divided  by (n-1)

• Select Tools | Data Analysis | Correlation
• Highlight the array to be considered or type A1:B6
• Select Labels in First Row
• Choose or click on the cell for the output.

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

• Click on the cell you want result to appear on.
• Click on function wizard icon (hit the function key ( f_x ) on the top toolbar)
• Highlight cells in the first array, here A1:A6
• Highlight cells in the first array, here B1:B6
• Select Okay.

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