CORREL

Returns the Pearson correlation coefficient of two sets of data.

Syntax:

CORREL(x, y)

where x and y are ranges or arrays containing the two sets of data.

Any text or empty entries are ignored.

CORREL calculates:

$\frac{\sum _{i} ( x _{i} - x ) ( y _{i} - y )}{\sum _{i} ( x _{i} - x ) ^{2} \sum _{i} ( y _{i} - y ) ^{2}}$

where $\overline{x}, \overline{y}$ are the averages of x, y.

Example:

CORREL(A1:A30, B1:B30)

returns the Pearson correlation coefficient for the two sets of data in A1:A30 and B1:B30.

Application:

The Relationship Between Study Hours and Exam Scores

Imagine you are a teacher and you want to see if there is a relationship between the number of hours students spend studying and their final exam scores. You collect data from 10 students in your class.

	Student	Study Hours (X)	Exam Score (Y)
	A	B	C
1	1	5	75
2	2	10	90
3	3	3	60
4	4	8	85
5	5	6	80
6	6	4	70
7	7	9	95
8	8	7	88
9	9	2	55
10	10	12	92

In this table, the "Study Hours" data is one array (let's call it Array1), and the "Exam Score" data is the second array (Array2).

To find the correlation between these two variables, you would use the CORREL function.

Formula:

CORREL(B1:B10, C1:C10)

Assuming your "Study Hours" data is in cells B1 through B10 and "Exam Score" data is in cells C1 through C10.

Result:

The CORREL function would return a value of 0.924009118.

Interpretation of the Result

The value is positive (+): This indicates a positive correlation. As one variable increases (study hours), the other variable also tends to increase (exam scores).
The value is close to +1: This indicates a strong positive linear relationship. The closer the value is to +1, the stronger the relationship.

In this example, the high correlation coefficient of approximately 0.924 suggests that there is a very strong positive relationship between the number of hours a student studies and their exam score. This means that, based on this data, students who study more tend to get higher exam scores.

Result for CORREL(B1:B10, C1:C10):

0.924009118