The CORREL function with example in Excel

The CORREL function in Excel calculates the correlation coefficient between two data sets. This coefficient measures the strength and direction of a linear relationship between two variables.

Syntax

excel
CORREL(array1, array2)

• array1: A range of cells containing the first data set.
• array2: A range of cells containing the second data set.

Example

Suppose you have two sets of data: one in column A (from A1 to A5) and another in column B (from B1 to B5). You want to calculate the correlation between these two sets of data.

Data

css
A         B
1   10       8
2   20      16
3   30      28
4   40      40
5   50      42

1. Input Data:

   • In cell A1 to A5, enter: 10, 20, 30, 40, 50
   • In cell B1 to B5, enter: 8, 16, 28, 40, 42

2. Using CORREL Function:

   • In a new cell (say, C1), enter the formula:

     excel
     =CORREL(A1:A5, B1:B5)
     

   • Press Enter.

3. Result:

   • The result will be approximately 0.9861.

Interpretation

• The correlation coefficient ranges from -1 to 1:
  • 1 indicates a perfect positive linear relationship.
  • -1 indicates a perfect negative linear relationship.
  • 0 indicates no linear relationship.
• In this example, a correlation coefficient of 0.9861 suggests a very strong positive linear relationship between the two data sets.

Step-by-Step Example

1. Input Data:

   • In cells A1 to A5, enter:

     1
     2
     3
     4
     5
     

   • In cells B1 to B5, enter:

     2
     4
     6
     8
     10
     

2. Using CORREL Function:

   • In cell C1, enter the formula:

     excel
     =CORREL(A1:A5, B1:B5)
     

3. Result:

   • The result in cell C1 will be 1.

Explanation

• The data in column B is exactly twice the data in column A. The correlation coefficient of 1 indicates a perfect positive linear relationship.

Example with Real-World Data

Suppose you want to analyze the relationship between the number of hours studied and the scores obtained in a test.

Data

css
Hours Studied  (A)     Test Score  (B)
1                2                 50
2                3                 55
3                5                 65
4                7                 70
5                8                 85

1. Input Data:

   • In cells A1 to A5, enter:

     2
     3
     5
     7
     8
     

   • In cells B1 to B5, enter:

     50
     55
     65
     70
     85
     

2. Using CORREL Function:

   • In cell C1, enter the formula:

     excel
     =CORREL(A1:A5, B1:B5)
     

3. Result:

   • The result in cell C1 might be approximately 0.9593.

Explanation

• A correlation coefficient of 0.9593 suggests a very strong positive linear relationship between hours studied and test scores.

These examples illustrate how the CORREL function can be used to determine the strength and direction of a linear relationship between two data sets in Excel.