Calculating Correlation in Microsoft Excel
Correlation is a statistical technique that can show whether and how strongly pairs of variables are related. For example, height and weight are related; taller people tend to be heavier than shorter people.
- Correlation is Positive when the values increase together, and
- Correlation is Negative when one value decreases as the other increases.
Like this :
Correlation can have a value:
- 1 is a perfect positive correlation
- 0 is no correlation (the values don’t seem linked at all)
- -1 is a perfect negative correlation.
The value shows how good the correlation is (not how steep the line is), and if it is positive or negative.
Example
Consider the following data samples :
X | Y |
---|---|
6
|
35
|
22
|
65
|
3
|
95
|
9
|
125
|
5
|
155
|
6
|
185
|
8
|
215
|
33
|
245
|
9
|
275
|
10
|
305
|
2
|
335
|
12
|
365
|
FORMULA :
CORREL(<range of first column>,<range of second column>)