Introduction
Earlier, we showed you what correlation looks like on scatter plots and described how the Pearson correlation coefficient, r, can vary from -1 to 1. In this post, we’ll provide some details on how to enter data, get the correlation coefficient, and get the p (significance) value for the correlation coefficient in Excel.
What You’ll Need
Microsoft Excel with the Analysis ToolPak.
Entering Data
Let’s say you have data on the heights (in inches) and weights (in pounds) of 15 people. The heights, in sequential order of your 15 subjects, are as follows: 67, 72, 75, 80, 60, 65, 68, 69, 69, 70, 70, 80, 76, 60, 60. The weights, in the same sequential order of the 15 subjects, are as follows: 150, 240, 270, 300, 160, 180, 170, 175, 175, 190, 190, 260, 240, 140, 130. Try typing these values into Excel in two named columns, as follows:
Getting the R Value
Having entered these variables into Excel, make sure the Analysis ToolPak is activated, then click on Data Analysis within the Analysis tab. There, select correlation as the procedure.
Next, select your data and tell Excel that your labels are in the first row:
Excel will create a new sheet for you, in the same document, with the correlation matrix:
Therefore, r = .9156, p < .0001. Remember, you can square your r value to get the coefficient of determination, which happens to be 0.8383. In other words, in your dataset, (0.9156)^2 or approximately 83.83% of the variation in weight is explained by variation in height.
Excel didn’t give you a p value for the correlation, but you can get the p value for a bivariate correlation by running a regression.
We’re not running a regression here—we just want a p value for our r of .9156. Therefore, it doesn’t matter which variable you select for Y and which for X. Just look for your p value in the regression readout, which will be on a new sheet:
The correlation is significant at p < .0001.
A Simple Scatterplot
If you want to create a scatterplot with height as the x axis and weight as the y axis, you will have to manually select the columns in Excel, go to insert charts, and click on the style of scatter plot you want. On the top right of the scatter plot, you can click on the plus sign to insert titles for your axes and for the scatter plot as a whole.
BridgeText can help you with all of your statistical analysis needs.
