How To Calculate Correlation Coefficient In Excel

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.

 

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.

Method two:

1. Select an empty cell.
2. Enter = to write the CORREL syntax.
3. Select the Array1 (first set of data) and Array2 (second set of data).
4. Press Enter.

Calculate the Correlation Coefficient by the PEARSON Function

Calculating the correlation coefficient by the PEARSON function is exactly like the way you use the CORREL function. Here, we have two sets of data, and we have two ways to enter the PEARSON function.

Method one:

7. Select the cell that you want to put the result.
8. Go to the Formulas tab.
9. Select the More Function button.
10. From the Statistical menu, pick the PEARSON function.
11. Now, select the Array1 (first set of data) and Array2 (second set of data).
12. Press OK.

Method two:

5. Select an empty cell.
6. Enter = to write the PEARSON formula.
7. Select the Array1 (first set of data) and Array2 (second set of data).
8. Press Enter.

 

 

Calculate the Correlation Coefficient by the Data Analysis

First, to enable the Data Analysis to follow these steps:

1. Go to the File tab from the ribbon.
2. Click on the Option. 
3. Click on the Add-ins.
4. From the Manage menu bar, select Excel Add-ins, and press Go.
5. Check the Analysis ToolPak checkbox and press OK.

https://www.youtube.com/watch?v=K4cah-grzGQ

Now you can find the Data Analysis option on the Data tab from the ribbon. So, Calculate the Correlation Coefficient:

1. Go to the Data tab. 
2. From the Analysis group, select the Data Analysis.
3. From the Data Analysis dialogue box, pick the Correlation, and press OK.
4. In the Correlation dialogue box, select the data range as the input range.
5. Check the columns or rows checkbox according to your data.
6. If your data sets have labels, check the Labels in first row checkbox.
7. Select an empty range to enter the Output range to see the result and press OK.

You can connect with us and ask our experts for your inquiries and get more Excel Support Services.

Reduce costs, accelerate tasks, and improve quality with Excel Automation Services.