Table of Contents
Partial correlation in Excel can be calculated by first entering the data into a spreadsheet, then using the Data Analysis tool to compute the correlation matrix with the Pearson correlation coefficient. After that, the partial correlation coefficient is calculated by subtracting the correlation coefficient of a given variable with all other variables from the correlation coefficient of that same variable with the two remaining variables. To do this, the CORREL and OFFSET functions can be used to calculate the partial correlation coefficient for each pair of variables.
In statistics, we often use the 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.
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.