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 FunctionHow 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
Share Share Tweet