In statistics, we often use the Pearson correlation coefficient to measure the linear relationship between two variables. However, sometimes we’re interested in understanding the relationship between two variables while controlling for a third variable.
For example, suppose we want to measure the association between the number of hours a student studies and the final exam score they receive, while controlling for the student’s current grade in the class. In this case, we could use a partial correlation to measure the relationship between hours studied and final exam score.
This tutorial explains how to calculate partial correlation in Excel.
Example: Partial Correlation in Excel
Suppose we have a dataset that shows the following information for 10 students:
- Current grade in a class
- Hours spent studying for the final exam
- Final exam score
Use the following steps to find the partial correlation between hours studied and exam score while controlling for current grade.
Step 1: Calculate each pairwise correlation.
First, we’ll calculate the correlation between each pairwise combination of the variables:
Step 2: Calculate the partial correlation between hours and exam score.
The formula to calculate the partial correlation between variable A and variable B while controlling for variable C is as follows:
Partial correlation = (rA,B – rA,C*rB,C) / √((1-r2A,B)(1-r2B,C))
The following screenshot shows how to use this formula to calculate the partial correlation between hours and exam score, controlling for current grade:
The partial correlation is 0.190626. To determine if this correlation is statistically significant, we can find the corresponding p-value.
Step 3: Calculate the p-value of the partial correlation.
The test statistic t can be calculated as:
t = r√(n-3) / √(1-r2)
The following screenshot shows how to use this formula to calculate the test statistic and the corresponding p-value:
The test statistic t is 0.51377. The total degrees of freedom is n-3 = 10-3 = 7. The corresponding p-value is 0.623228. Since this value is not less than 0.05, this means the partial correlation between hours and exam score is not statistically significant.
