How to Calculate Correlation

A central research question is the extent to which one variable relates to another. Does socioeconomic status have any effect on student academic achievement? What is the relationship between education and earnings? These and other questions ask if a correlation exists between two variables. When a correlation exists, changes in one variable are associated with changes in another. The steps presented here demonstrate how to calculate correlation between two variables of interest.

Instructions

1. Enter your data into a spreadsheet program, such as Microsoft Excel. For example, let's say you want to examine the correlation between math test scores and number of tutoring hours in math for a group of 30 students. On your spreadsheet, let each stand represent one row of data. Enter each student's number of tutoring hours in one column and math test scores in another column.

2. Calculate the mean number of tutoring hours for the 30 students and the mean mathematics scores. To do this, add the number of tutoring hours and divide by 30 (30 students). Then add the math scores and divide by 30.

3. Visually plot your data using a scatterplot. Many spreadsheet programs, such as Excel, allow you to display your data graphically. You can do this in Excel by selecting the chart wizard, under the "Insert" menu. Then select "Scatterplot" and then select a data range. Select a range by clicking on a cell in the spreadsheet and dragging your computer mouse to cover all cells you want included. For our example, let's say you entered each student's tutoring hours in Column A and the corresponding math scores in Column B (remember that each student represents one row). So for this example, you would select columns A and B. Excel will then display a scatterplot that shows your data points on X and Y axes. For our example here, let Y = math scores and X = number of tutoring hours.

4. Calculate your correlation coefficient. The data analysis tool in Excel can do this for you. In Excel, under the Tools menu, choose "Data Analysis." When the Analysis Menu pops up, select "Correlation." Indicate the range of cells in your spreadsheet you want analyzed, just as you did with your scatterplot in Step 3. Briefly stated, the correlation coefficient is the sum of the differences between values of X and the mean of X (X being tutoring hours) divided by the standard deviation of X, times the sum of the differences between values of Y and the mean of Y (Y being math scores) divided by the standard deviation of Y, all divided by the number in your sample minus 1 (in this example, 30 students minus 1, or 29).

5. Interpret your results. The correlation coefficient has boundaries of -1 and +1. A value of +1 indicates perfect positive relationship, while -1 is a perfect linear relationship. In our example, the correlation coefficient will likely fall somewhere in between. A value of zero indicates no correlation. A positive number between zero and 1 suggests that the more tutoring a student receives, the higher his math score. The higher the number, the higher the level of correlation. A negative number would suggest that tutoring may have a negative effect on math scores.

Tips & Warnings

• You may have to install the data analysis tool in Excel before you can use it. To do this, go to "Add-Ins" in the Tools menu and select "Analysis Tool Pak."

Resources

• Calculating Correlation with Excel