How to Calculate Partial Correlation Matrix With Excel VBA?

Correlation is the way to measure the relation between two variables. The value of the correlation lies between -1 to 1. If the value is greater than0 then both the values are positively correlated, if the value of the correlation is 0 then there is no such relation between the two variables and if the value is less than 0 then the two values are negatively correlated.

Partial Correlation is used to measure the relationship between two different variables by eliminating the third variable. The partial correlation matrix calculates the coefficients of partial correlation of a matrix. In the partial correlation matrix row i and column j have a partial correlation with the row i and column j of the original matrix. In this article, we will discuss calculating a partial correlation matrix in Excel VBA.

Following are the steps to calculate the partial correlation matrix with Excel VBA are:

Step 1: Given sample data, which has 5columns named V1, V2, V3, and V4. Each column has 7 rows, with different numbers in it.

Given data to calculate partial correlation matrix

Step 2: Before calculating the partial correlation matrix, we need to calculate the correlation matrix for the given data. Make a new table, where H4 = V1, I4 = V2, J4 = V3, and K4 = V4. Similarly, G5 = V1, G6 = V2, G7 = V3, and G8 = V4.

making a new table to add correlation matrix

Step 3: Enter the following formula in the H5 cell to calculate the correlation of the variables. We have an in-built function in Excel, to calculate the correlation matrix i.e. =correl(array1, array2). Now, we have to pass arguments, in the correl function, which can easily be achieved using the offset function, as it takes a range of elements.

=CORREL(OFFSET($A$2:$A$8, ,ROWS($1:1)-1),OFFSET($A$2:$A$8, ,COLUMNS($A:A)-1))

correlation formula added, using offset, rows and column function

Step 4: After getting the correlation for the V1 row and column, drag the cell from H5 to K8. The same formula as in H5, will be copied to all other cells.

dragging the correlation formula from a cell to the entire matrix

Step 5: For calculating the partial correlation matrix we need to first create a table. Make a new table, where O4 = V1, P4 = V2, Q4 = V3, and R4 = V4. Similarly, N5 = V1, N6 = V2, N7 = V3, and N8 = V4. Then, press Alt + F11 to get the VBA Code Editor.

making a new table, to calculate partial correlation matrix

Step 6: Click onthe Insert tab and select Module.

opening vba editor and creating a new module

Step 7: Enter the following code in the Module to calculate the Partial Correlation matrix. Knowing the working of the code is not so important, because this is a general code, and can work for any table. After adding the code, close the VBA window.

C++

Option Explicit

Function Partial_Cor(Rng As Range)

    Dim r As Long, c As Long
    Dim i As Long, j As Long
    
    Dim R As Variant
    Dim Rinv As Variant
    Dim D() As Double
    Dim Dsqrt() As Double
    Dim Temp As Variant
    Dim PartialCorr() As Double
    
    // Load matrix from range
    
    R = Rng.Value
    r = UBound(R, 1)
    c = UBound(R, 2)

    // Must be square matrix
    
    If r <> c Then
        Partial_Cor = CVErr(xlErrValue)
        Exit Function
    End If

    // Invert the matrix
    
    On Error Resume Next
    Rinv = Application.MInverse(R)
    If Err.Number <> 0 Then
        Partial_Cor = CVErr(xlErrValue)
        Exit Function
    End If
    On Error GoTo 0
    
    // Allocate diagonal vectors
    
    ReDim D(1 To r)
    ReDim Dsqrt(1 To r)
    ReDim PartialCorr(1 To r, 1 To c)
    
    // Extract diagonal of inverse matrix and compute reciprocals
    
    For i = 1 To r
        D(i) = 1 / Rinv(i, i)
        Dsqrt(i) = Sqr(D(i))
    Next i
    
    // Build: D^1/2 * Rinv * D^1/2
    
    Dim DiagSqrtLeft() As Double, DiagSqrtRight() As Double
    
    ReDim DiagSqrtLeft(1 To r, 1 To c)
    ReDim DiagSqrtRight(1 To r, 1 To c)
    
    For i = 1 To r
        For j = 1 To c
            DiagSqrtLeft(i, j) = 0
            DiagSqrtRight(i, j) = 0
            
            If i = j Then
                DiagSqrtLeft(i, j) = Dsqrt(i)
                DiagSqrtRight(i, j) = Dsqrt(i)
            End If
        Next j
    Next i
    
    // Multiply: D½ * Rinv
    
    Temp = Application.MMult(DiagSqrtLeft, Rinv)
    
    // Multiply: (D½ * Rinv) * D½
    
    Temp = Application.MMult(Temp, DiagSqrtRight)
    
    // Now compute Partial Correlation: P = I - Temp
    
    For i = 1 To r
        For j = 1 To c
            If i = j Then
                PartialCorr(i, j) = -1
            Else
                PartialCorr(i, j) = -Temp(i, j)
            End If
        Next j
    Next i
    
    Partial_Cor = PartialCorr

End Function

Step 8: Now, select the cells, from range O5:R8.

select the entire matrix of partial correlation table

Step 9: As we can see in the VBA code, the name of the function is Partial_Cor. Enter =Partial_Cor(H5:K8) in the O5 cell and then click Ctrl+Shift+Enter.

add partial_cor function, written in VBA

Step 10: We will get the Partial Correlation Matrix.

partial correlation matrix successfully created.

How to Calculate Partial Correlation Matrix With Excel VBA?

Explore