Calculating the Correlation Coefficient

So far we have got an impression of how strongly associated two variables are by looking at a scatter chart. The next stage is to measure the degree of closeness by calculating a Correlation Coefficient.

This is a measure, denoted by r, which can take values between –1 and +1. The value:

r = +1 corresponds to perfect positive correlation (the points lie exactly on a straight line)
r = 0 corresponds to no correlation
r = -1 corresponds to perfect negative correlation.

In general, the closer the correlation coefficient is to plus or minus 1, the closer the points are to a straight line; the closer the coefficient is to zero, the more scattered the points.

Pearson’s Product Moment Correlation Coefficient

This uses the actual values, and is the measure that is most widely used. The full formula for calculating it is fairly daunting. Many spreadsheets and some calculators have a regression option that calculates r (or r squared) directly. If you do not have this option you can still use your calculator or spreadsheet to calculate the basic building blocks.

Let us try to calculate the Pearson correlation coefficient for the third example (see Table 5.2, unit cost against volume of business).

If you are using a spreadsheet, then you will first have to enter the data as three columns:

Time Period - Column A
Volume of Business - Column B
Unit Cost - Column C

a) To find the correlation coefficient using Excel, use the function
= CORREL (array 1, array2)

This will give r = -0.968 which is a strong negative association.

It is also possible to display r ² on a scatter chart created in Excel, by clicking the right hand mouse button on a data point, select Add Trendline -> Linear

Taking the Options tab click in the box Display R-Squared Value on chart.

To obtain the correlation coefficient it is necessary to take the square root of r ² and decide whether it is positive or negative from the direction of the scatter chart.

b) If your spreadsheet does not have a regression function, you can still use it to do most of the tedious calculations and then use the formula;

where

is the mean of the x values
is the mean of the y values
are SDs of the x and y values respectively
n is the number of pairs of data

You should get a table similar to that shown below

Example 1

Table 5.3 Setting out the calculation of a correlation coefficient using a spreadsheet (if no regression function)

Time Period
Volume Business
X
Unit Cost (MWK)
Y
x*y
1
2465
7.93
19547
2
2699
7.33
19784
3
2389
7.75
18515
4
2408
7.55
18180
5
2811
6.60
18553
6
2209
8.40
18556
7
2982
5.98
17832
8
2802
6.87
19250
9
2966
6.48
19220
10
3182
5.94
18901
11
2356
8.26
19461
12
3262
5.87
19148
13
2865
6.63
18995
14
2484
7.36
18282
15
2133
9.02
19240
Total
40013
107.97
283464
Mean
2667.533
7.198
-
SD
336.389
0.932
-

Total is found by using =sum( )
Mean is found by using = average( )
SD is found using = stdevp ( )

Many calculators have LR (or Reg) mode and will give you r directly. If your calculator only has SD mode then find the mean and SD of each variable and . Then use the formula as above.

Example 2

The cost of output at a factory is thought to depend on the number of units produced. Data have been collected for the number of units produced each month in the last six months, and the associated costs, as follows;

Output
(‘000s of units) X
Cost
($’000) Y
2
9
3
11
1
7
4
13
3
11
5
15

Solution

X
Y
XY
X²
Y²
2
9
18
4
81
3
11
33
9
121
1
7
7
1
49
4
13
52
16
169
3
11
33
9
121
5
15
75
25
225
∑X = 18
∑Y = 66
∑XY= 218
∑X2 = 64
∑Y2 = 766

n = 6

so . . .

There is a perfect positive correlation between the volume of output at the factory and costs.