Copyright

How to Calculate the Correlation Coefficient in Excel

Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems and has a PhD in Information Technology Management. He is an adjunct professor of computer science and computer programming.

How are the sales of coffee related to football games? In this lesson, we try to answer this question by using Excel to calculate the correlation coefficient between two variables.

Correlation Coefficient

The correlation coefficient measures the association between two variables. Correlations are shown as values between -1.0 and 1.0, from no correlation to positive correlation. In other words, there is less correlation with a number closer to -1 and a higher correlation as you get closer to 1.

Correlation DOES NOT EQUAL causation! It is merely a value to show how two variables move when compared to one another. For example, coffee sales have a positive correlation to traffic delays. Now, we can't say that coffee purchases cause traffic jams…but, in reality, most people buy coffee in the morning on their way to work.

So, how do we calculate a correlation coefficient between two variables? There are advanced statistical software packages available, such as SPSS. Or, we could use Excel.

First, let's look at our data. We will compare coffee sales to the wins/losses of the local football team. Since we have to have numeric data, we'll represent a win with a 1 and a loss with a 0. Here's the data:


Correlation coefficient - data


We've left a spot for the correlation coefficient (in cell D19).

There are a couple of ways to solve this problem. First, we will look at the Excel CORREL function, then we will use the Analysis Toolpak Add-in. It is important to note that both functions REQUIRE numeric values! Since we are trying to calculate a numeric relationship, we need to have numeric data points. This is why we converted wins and losses to 1s and 0s.

CORREL Function

The CORREL function in Excel is accessed by either typing =CORREL in the function box (that's the long text box labeled fx) or clicking on the fx label in order to bring up the list of functions. Let's do that because it also brings up a nice little wizard to help us.

First, click in cell D19 (this is where we will display the output); then click fx.


Excel click function


Here you can select CORREL from the list of functions or search for it in the Search box. If you have used the function recently, it will already be on the list. Here, we are using it for the first time and searching for it.


Find CORREL function


Once CORREL is selected, click OK, which will bring you to the next step in the wizard.

  1. Click in the box for Array1 and then select all values from Column B (Win/Loss)
  2. Click in Array2 and select values from Column C (Sales)

This tells Excel that you want to perform a correlation analysis on the win/loss vs the sales data. You'll notice that it gives you the result right away.


CORREL enter data arrays


After you are sure of your data set, click OK to populate cell D19.


CORREL Final value


Analysis Toolpak

Another option for calculating the correlation coefficient is an Add-in to Excel. It is available to all, but you just need to make sure you enable it. In order to get the Add-in enabled, click on the File tab in Excel, then click Options:


Excel file options


Next, select Add-ins:


Excel select add-ins


If you have not yet enabled this Add-in, it will display under Inactive Application Add-ins:


Excel inactive addins


In order to activate, scroll down and locate the Manage drop-down. Make sure Excel Add-ins is selected, and then click Go.


Excel add-ins add


Select Analysis TookPak, then click OK.


Add ins select ToolPak


Return to your worksheet and you will see a new option under the Data ribbon, called Data Analysis.


Excel data analysis


To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support