How to Find Correlation Coefficient in Excel

What is the Correlation Coefficient?

The correlation coefficient of a data set is a statistical number that tells how strongly two variables are related to each other. It can be said that it is the percentage of the relation between two variables (x and y). It can't be greater than 100% and less than -100%.

The correlation coefficient falls between -1.0 and +1.0.

A negative correlation coefficient tells us that if one variable increases, other variable decreases. A correlation of -1.0 is a perfect negative correlation. This means that if x increases by 1 unit, y decreases by 1 unit.

A positive correlation coefficient tells us that if one variable's value increases, other variable's value also increases. This means that if x increases by 1 unit, y also increases by 1 unit.

The correlation of 0 says that there is no relation between two variables what so ever.

The Mathematical formula of Correlation Coefficient is:

=Coveriancexy/(Stdx*Stdy)

Coveriancexy is the covariance (sample or population) of data set.

Stdx= It is Standard Deviation (sample or population) of Xs.

Stdy= It is Standard Deviation (sample or population) of Ys.

How to Calculate the Correlation Coefficient in Excel?

If you need to calculate the correlation in excel, you do not need to use the mathematical formula.  You can use these methods

  1. Calculating Correlation Coefficient using COREL function.
  2. Calculating Correlation Coefficient using Analysis Toolpak.

Let's see an example to know how to calculate the correlation coefficient in excel.

Example of Calculation of correlation coefficient in excel

Here I have a sample data set. We have xs in range A2:A7 and ys in B2:B7.

We need to calculate the correlation coefficient of xs and ys.

Using Excel CORREL Function

Syntax of the CORREL function:

=CORREL(array1,array2)

array1: This is the first set of values (xs)

array2: It is the second set of values (ys).

Note: the array 1 and array 2 should be of the same size.

Let's use the CORREL function to get the correlation coefficient. Write this formula in A10.

=CORREL(A2:A7,B2:B7)

We get a correlation of 0.356448487 or 36% between x and y.

Using Excel Analysis Toolpak

To calculate correlation using analysis toolpak follow these steps:

  • Go to the Data tab on the ribbon. To the left most corner, you will find the data analysis option. Click on it. If you can't see it, you first need to install the analysis toolpak.

  • From the available options, select Correlation.

  • Select the input range as A2:B7. Select the output range where you want to see your output.

 

  • Hit OK button. You have your correlation coefficient in the desired range. It is the exact same value as returned by the CORREL function.

How correlation is being calculated?

To understand how we are getting this value, we need to find it manually. This will clear our doubts.

As we know the correlation coefficient is:

=Coveriancexy/(Stdx*Stdy)

First, we need to calculate the covariance. We can use the COVERIACE.S function of excel to calculate it.

=COVARIANCE.S(A2:A7,B2:B7)

Next, let's calculate the standard deviation of x and y using the STDEV.S function.

=STDEV.S(A2:A7)

 

=STDEV.S(A2:A7)

Now in cell D10, write this formula.

=D3/(D5*D7)

This is equivalent to =Covariancexy/(Stdx*Stdy). You can see that we get the exact same value as given by the CORREL function. Now you know how we have derived the correlation coefficient in excel.

Note: In the above example, we have used COVARIANCE.S (covariance of the sample) and STDEV.S (standard deviation of the sample). The correlation coefficient will be the same if you use COVARIANCE.P and STDEV.P.  As long as they both are of the same category there will be no difference. If you use COVARIANCE.S (covariance of the sample) and STDEV.P (standard deviation of the population) then the result will be different and incorrect.

So yeah guys, this is how we can calculate correlation coefficient in excel. I hope this was explanatory enough to explain the correlation coefficient. You can now create your own correlation coefficient calculator in excel.

Related Articles:

Calculate INTERCEPT in Excel

Calculating SLOPE in Excel

Regressions in Excel

How to Create Standard Deviation Graph

Descriptive Statistics in Microsoft Excel 2016

How to Use Excel NORMDIST Function

Pareto Chart and Analysis

Popular Articles:

50 Excel Shortcut to Increase Your Productivity

The VLOOKUP Function in Excel

COUNTIF in Excel 2016

How to Use SUMIF Function in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube