Calculating Partial Correlation Matrix with Excel VBA

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. Compute 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 Execute

1. Open Excel. Press Alt F11 to open VB editor

2. Go to Insert >> Module

3. In module, paste the above code to VB editor

4. Close VB editor window

5. Select the range of a table M15:P18

6. Enter =PartialCor(G3:J6) and Hit Ctrl Shift Enter

7. After pressing Ctrl Shift Enter you will find {=PartialCor(G3.:J6)}

across all the cells in matrix

Download this workbook

Related link : Partial Correlation using Formula

Spread the Word!
Share Share Tweet

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 has worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and Human Resource.

While I love having friends who agree, I only learn from those who don't
Let's Get Connected Email LinkedIn

Calculating Partial Correlation Matrix with Excel VBA

Post a Comment