Table of Contents
To calculate partial correlation in Excel, you can use the CORREL function along with the Analysis ToolPak add-in. First, enable the Analysis ToolPak add-in in Excel by going to File > Options > Add-ins > Manage: Excel Add-ins > Go. Then, in a new worksheet, enter the data for the three variables you want to calculate the partial correlation for. Next, use the CORREL function to calculate the correlation between the two variables of interest. Finally, use the partial correlation formula, which involves subtracting the product of the two variables’ correlations with the third variable from the correlation between the two variables, to determine the partial correlation value. This process can be repeated for multiple sets of variables to calculate their respective partial correlations.
Calculate Partial Correlation in Excel
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.