This tutorial shows VBA code for the user-defined function (UDF) to calculate Partial Correlation Matrix.

**Partial Correlation**

It 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**

## Post a Comment