PRESS Statistic, Hat Matrix, and Leverage Points for Multiple Linear Regressions in Excel

multipleLinearAnalysis.bas

' Multiple Linear Regressions in Excel
' This group of functions uses the Hat Matrix and 
' Leverages to calculate the PRESS statistic
' A column of ones must precede your X columns (exR)
' https://en.wikipedia.org/wiki/PRESS_statistic
' https://en.wikipedia.org/wiki/Projection_matrix
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Function Hat(exR As Range) As Variant
    'Returns a symmetrical array of dimensions n by n
    '{=MMULT(MMULT(ex,MINVERSE(MMULT(TRANSPOSE(ex),ex))),TRANSPOSE(ex))}
    With WorksheetFunction
        Hat = .MMult(.MMult(exR, .MInverse(.MMult(.Transpose(exR), exR))), .Transpose(exR))
    End With
End Function

Function Leverages(exR As Range) As Variant
    'Diagonal elements of the Hat matrix
    'The influence each response value has on the fitted value for that same observation
    Dim H As Variant
    Dim result() As Double
    Dim i As Integer
    
    ReDim result(1 To exR.Rows.Count, 1 To 1)
    H = Hat(exR)
    
    For i = 1 To exR.Rows.Count
        result(i, 1) = H(i, i)
    Next i
    
    Leverages = result
End Function

Function Press(exR As Range, yR As Range) As Double
    'Returns Press Statistic (lower is better)
    '{=SUMSQ((MMULT(Hat(ex),yCol)-yCol)/(1-Leverages(ex)))}
    Dim yPred As Variant
    Dim Hii As Variant
    Dim i As Integer
    
    yPred = WorksheetFunction.MMult(Hat(exR), yR.Value)
    Hii = Leverages(exR)
    
    For i = 1 To exR.Rows.Count
        Press = Press + ((yPred(i, 1) - yR.Cells(i).Value) / (1 - Hii(i, 1))) ^ 2
    Next i
End Function