How To Calculate Correlation Coefficient In Excel

3.8 min read|Last Updated: April 30th, 2024|Categories: excel|
table of content

In experiments, the next step after gain variables is finding relevance intensity among them. Excel makes the calculations easy by using the functions or data analysis tools In this tutorial, We’ll know what the correlation coefficient is and how it works and then clarify the calculation process in Excel.

What Is the Correlation Coefficient?

The correlation coefficient (a value between -1 and 1) tells you about the relationship between the two variables.

How the Correlation Coefficient shows the relationship between two variables?
Picture 1- The Correlation Coefficient

 

According to the picture () 

  • -1≤ρ<0: means the negative linear correlation (a negative direction relevance between variables)
  • 0<ρ≤1: means the positive linear correlation (a positive direction relevance between variables)
  • ρ=0: means no linear correlation (no relevance between variables)

Another way to figure the relationship between two variables is Covariance. But the amount of Covariance is affected by the units of variables measurement, making it challenging to compare large numbers. To standardize the amount of dependence between two variables, we use the correlation coefficient.

The Correlation Coefficient Formulas

We have two formulas for calculating the correlation coefficient in Excel.

  1. CORREL
  2. PEARSON

The CORREL formula is more accurate than the PEARSON function and is especially suitable for huge numbers.

Calculate the Correlation Coefficient by the CORREL Function

Method one:

Here we have two sets of data. So, to calculate the correlation coefficient, follow these steps:

  1. Select the cell that you want to put the result.
  2. Go to the Formulas tab.
  3. Select the More Function button.
  4. From the Statistical menu, pick the CORREL function.
  5. Now, select the Array1 (first set of data) and Array2 (second set of data).
  6. Press OK.