Excel Macro for Partial Correlation Matrix

Deepanshu Bhalla Add Comment , ,

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
  1. Open Excel and press Alt + F11 to open the Visual Basic (VB) editor.
  2. In the VB editor, go to Insert and choose Module to insert a new module.
  3. Copy and paste the provided code into the VB editor window.
  4. Close the VB editor window.
  5. Select the range of a table, for example, M15:P18.
  6. Enter the formula =PartialCor(G3:J6) into a cell and press Ctrl + Shift + Enter to enter it as an array formula.
  7. After pressing Ctrl + Shift + Enter, you will see the formula displayed as {=PartialCor(G3:J6)} with curly braces across all the cells in matrix.
VBA code for Partial Correlation Matrix
Download this workbook
Related link :Partial Correlation using Formula
Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

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.

Post Comment 0 Response to "Excel Macro for Partial Correlation Matrix"
Next → ← Prev