How to Use the CORREL Function in Microsoft Excel
Suppose you are a teacher and you want to see if there is a relationship between a student's average grade on homework assignments and his or her average grade on exams. Trying to figure this out using paper, a pencil, and a calculator is tedious and time-consuming, and you might even make mistakes along the way. This article explains how to use Excel's correlation function (CORREL) to determine the strength and direction of a linear relationship between two variables.
Other People Are Reading
Things You'll Need
- data
- Microsoft Excel (any version will work; I have Microsoft Excel XP)
- basic knowledge of Excel and functions
Instructions
-
-
1
Start Microsoft Excel. You can do this by clicking on Start, highlighting Programs, and clicking Microsoft Excel, or double-clicking the Microsoft Excel icon on your computer's desktop.
-
2
Before we work with data, it's important to understand an important fact about the correlation coefficient, the value that represents the strength of the relationship between two random variables. The correlation coefficient ranges from -1 to +1, with -1 indicating a perfect negative correlation and +1 indicating a perfect positive correlation.
-
-
3
Enter the data. I'll use cells A1 to A25 and B1 to B25 for this purpose. The A column represents each student's homework average and the B column refers to each student's exam average.
-
4
Enter the following values, starting with cell A1 and ending with cell A25: 89, 92, 88, 76, 90, 68, 100, 87, 93, 77, 81, 80, 94, 90, 83, 79, 73, 83, 91, 84, 88, 90, 93, 80, 91.
-
5
Now enter the following values, starting with cell B1 and ending with cell B25: 76, 83, 80, 84, 81, 90, 92, 78, 70, 93, 82, 90, 98, 75, 67, 72, 90, 82, 77, 81, 87, 63, 92, 71, 82.
-
6
Now we can calculate the correlation coefficient. In cell A27, type =CORREL(A1:A25, B1:B25). A1 to A25 and B1 to B25 is where the data appears. Hit ENTER.
-
7
The result is -.07965, indicating a very slight weak relationship between the two variables.
-
1
Tips & Warnings
There is a different way to determine correlation with Microsoft Excel. Click Tools, click Data Analysis, select Correlation, and click OK. The blinking cursor is in the Input Range box. Highlight cells A1 through B25. This is denoted by $A$1:$B$25. Decide where you want your output to appear and click OK. The result is still -.07965.
Take a moment to ensure you didn't enter any of the values incorrectly.
Make sure you enter the numbers for each list in the same order. If the numbers are out of order, the correlation coefficient will be inaccurate.
