Yousuf Khan
Microsoft Excel is a powerful software. We use Excel functions and features for our educational, business, and other mathematical analyses such as stock markets, measurements, etc. Furthermore, we can determine the partial correlation coefficients for finding the relationship between variables using the Excel formula and VBA approaches. With that in mind, let’s start from scratch and learn 3 suitable ways to calculate partial correlation in Excel.
Download Practice Workbook
Download this practice workbook to exercise while reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the whole process.
Introduction to Partial Correlation
By minimizing the impact of the third variable, partial correlation determines the correlation between the 2 variables. There may be circumstances where there are numerous relationships between the variables. This might make correlation less accurate or even produce inaccurate results. The effects of other variables are eliminated via partial correlation. Let’s consider X and Y, two variables for which correlation needs to be determined. Think about the third variable, Z, which is connected to X and Y. The equation:
Where,
- X, Y, Z are three arbitrary variables.
- Partial Correlation (X, Y). Z = The partial correlation of the X variable & Y variable controlled by the Z variable.
3 Suitable Ways to Calculate Partial Correlation in Excel
When two variables are conditional on one or more other factors, partial correlation measures the correlation between the two variables. Meanwhile, we can compute the partial correlation using the CORREL function, a formula based on the correlation equation, and a VBA approaches. To demonstrate, we take a dataset that represents a student evaluation sheet. In this article, we can use a partial correlation to find the relationship between the student’s CGPA, SAT Score, and Assessment.
1. Utilize CORREL Function to Determine Partial Correlation in Excel
The first method aims to apply the CORREL function for determining the partial correlation coefficient. The CORREL function is under the Excel Statistical function category and calculates the correlation coefficient of the given cell ranges. Let’s follow the instructions carefully to implement the method.
Steps:
- First of all, add 2 columns F and G, and name the headers as Residual-1 and Residual-2 respectively.
- Here, we will use a regressional analysis model based on the dependent variable CGPA and independent variables SAT Score and Assessment.
- Also, we will use the INTERCEPT and the SLOPE functions for the regression model.
- Now, write the following formula in cell F5,
=D5-(INTERCEPT($D$5:$D$9,$C$5:$C$9)+SLOPE($E$5:$E$9,$C$5:$C$9)*C5)
- After that, press the Enter key to see the output.
- Later, use the AutoFill icon to fill the rest of the column.
- Likewise, in cell G5, type:
=E5-(INTERCEPT($E$5:$E$9,$C$5:$C$9)+SLOPE($E$5:$E$9,$C$5:$C$9)*C5)
- Pressing the Enter button will display the output.
- Again, copy the formula cell down with the AutoFill tool.
- Further, to determine the partial correlation based on the residuals, type
=CORREL(F5:F9,G5:G9)
- Meanwhile, press Enter.
- Lastly, the result appears in our dataset.
- INTERCEPT($E$5:$E$9,$C$5:$C$9) calculates the location where column E will intersect column C.
- SLOPE($E$5:$E$9,$C$5:$C$9) returns the slope of the linear regression line through the known C column and known column E’s data points.
Read More: How to Calculate Correlation Coefficient in Excel (3 Methods)
Similar Readings
- How to Interpret Correlation Table in Excel (A Complete Guideline)
- Calculate Pearson Correlation Coefficient in Excel (4 Methods)
- How to Calculate P Value for Spearman Correlation in Excel
- Make a Correlation Matrix in Excel (2 Handy Approaches)
- How to Do Correlation and Regression Analysis in Excel
2. Use Dynamic Correlation Equation to Calculate Partial Correlation in Excel
The objective of this method is to use the correlation equation mentioned previously and determine the partial correlation. Moreover, we will the SQRT function in the equation to calculate the variables. The SQRT function returns the square roots of integers. Therefore, follow the easy steps to see how it works.
Steps:
- First, let’s determine the coefficients of the given matrices.
- Consequently, in cell E11, type:
=CORREL(D5:D9,E5:E9)
- Now, tap the Enter key.
- Similarly, in cell E12, write the formula below.
=CORREL(D5:D9,C5:C9)
- Further, press Enter.
- Likewise, in cell E13, input the formula:
=CORREL(E5:E9,C5:C9)
- By pressing the Enter or Tab key, we get the coefficients.
- Meanwhile, let’s calculate the partial correlation based on the obtained coefficients using the following formula in cell E15.
=(E11-E12*E13)/(SQRT(1-E12^2)*SQRT(1-E13^2))
- Lastly, press the Enter key one more time.
- Thus, we obtain our desired result.
Read More: How to Calculate Spearman Correlation in Excel (3 Easy Methods)
3. Run a VBA Code to Compute Partial Correlation in Excel
In our last method, we will thoroughly calculate the partial correlation using Excel VBA. Furthermore, we will see the use of VBA functions and objects in this method. Let’s dive into the instructions to run the code perfectly.
Steps:
- Firstly, go to the Developer tab and click Visual Basic.
- Subsequently, the Visual Basic window pops up.
- Later, tap the Insert tab and then Module.
- Eventually, the module box appears.
- There, type the following VBA code.
Sub SOFTEKO_PAR_CORR()
Range("E11").Select
ActiveCell.FormulaR1C1 = "=CORREL(R[-6]C[-1]:R[-2]C[-1],R[-6]C:R[-2]C)"
Range("E12").Select
ActiveCell.FormulaR1C1 = "=CORREL(R[-7]C[-1]:R[-3]C[-1],R[-7]C[-2]:R[-3]C[-2])"
Range("E13").Select
ActiveCell.FormulaR1C1 = "=CORREL(R[-8]C:R[-4]C,R[-8]C[-2]:R[-4]C[-2])"
Range("E15").Select
ActiveCell.FormulaR1C1 = _
"=(R[-4]C-R[-3]C*R[-2]C)/(SQRT(1-R[-3]C^2)*SQRT(1-R[-2]C^2))"
Range("E16").Select
End Sub
- Lastly, press the green Run button.
- As a result, the desired outputs pop up on the display.
Read More: How to Calculate Cross Correlation in Excel (2 Suitable Ways)
Conclusion
In conclusion, we have discussed some suitable ways to calculate partial correlation in Excel. Not to mention, our ExcelDemy website shows various simple problem-solving methods like this. Please leave any further queries or recommendations in the comment box below.
