This tutorial shows VBA code for the user-defined function (UDF) to calculate Partial Correlation Matrix.
Partial CorrelationIt is simply defined as the measure of the relationship between two or more variables while controlling for the effects of one or more additional variables.
For example, study of partial correlation between price and demand would involve studying the relationship between price and demand excluding the effect of price of related goods, disposable income, taste and preference etc.Derivation
1. Compute correlation matrix
=CORREL(OFFSET(firstvariable_range,,ROWS($1:1)-1),OFFSET(firstvariable_range,,COLUMNS($A:A)-1))
For example, you have a 4 X 4 matrix and data for variables are entered in cells A2:D8 so paste this formula in cell G3 =CORREL(OFFSET($A$2:$A$8,,ROWS($1:1)-1),OFFSET($A$2:$A$8,,COLUMNS($A:A)-1))
In this case, firstvariable_range is A2:A8.
For detailed explanation, visit this link Correlation Matrix using Excel Formula
Download the workbook used in this example and play around with the formula to test your understanding.
2. VBA Code to calculate Partial correlation matrix
You can write an Excel macro to calculate partial correlation matrix. See the VBA code below for the user-defined function for Partial Correlation Matrix.
Option Explicit
Function PartialCor(R)
Dim rows As Integer, cols As Integer
Dim Identity() As Double, RDiag() As Double, RDiagSQRT() As Double, _
Part_Cor() As Double, Neg_Cor As Variant, RInverse As Variant
Dim I As Integer, J As Integer
rows = R.rows.Count
cols = R.Columns.Count
RInverse = Application.MInverse(R)
ReDim Identity(1 To rows, 1 To cols)
ReDim RDiag(1 To rows, 1 To cols)
ReDim RDiagSQRT(1 To rows, 1 To cols)
ReDim Part_Cor(1 To rows, 1 To cols)
For I = 1 To rows
For J = 1 To cols
Identity(I, J) = 0
RDiag(I, J) = 0
If I = J Then
Identity(I, J) = 1
RDiag(I, J) = 1 / RInverse(I, J)
RDiagSQRT(I, J) = RDiag(I, J) ^ 0.5
End If
Next J
Next I
Neg_Cor = (Application.MMult(RDiagSQRT, Application.MMult(RInverse, RDiagSQRT)))
For I = 1 To rows
For J = 1 To cols
Part_Cor(I, J) = Identity(I, J) - Neg_Cor(I, J)
Part_Cor(I, I) = -1
Next J
Next I
PartialCor = Part_Cor
End Function
How to use VBA Code- Open Excel and press Alt + F11 to open the Visual Basic (VB) editor.
- In the VB editor, go to Insert and choose Module to insert a new module.
- Copy and paste the provided code into the VB editor window.
- Close the VB editor window.
- Select the range of a table, for example, M15:P18.
- Enter the formula =PartialCor(G3:J6) into a cell and press Ctrl + Shift + Enter to enter it as an array formula.
- After pressing Ctrl + Shift + Enter, you will see the formula displayed as {=PartialCor(G3:J6)} with curly braces across all the cells in matrix.
Download this workbook
Related link :Partial Correlation using Formula
Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.
Share Share Tweet