I am looking to create a User Defined Function within excel to run
multiple OLS regression without having to use array formulas within
the spreadsheet. I want a UDF called Beta1, which will give me the
beta associated with the first
independent variable. The formula for the beta vector is b = (X'X)^-1
X'y. I am getting as far as (X'X) without problem. But I can not use
the MInverse function correctly. I assume it has something to do with
how the variables are declared, but I dont know for sure. I need
someone to get me to to a point where the function beta1 will give the
correct output.

Thanks. Email me with any questions.
********************************************************
Function Beta1(Dependent As Range, independent As Range)

Dim IndRows As Long
Dim IndCols As Long
Dim n As Integer
Dim k As Integer
Dim i As Integer
Dim j As Integer
Dim sum As Integer
Dim sumT As Long

'find the number of usuable observations

IndRows = independent.Rows.Count
IndCols = independent.Columns.Count
ReDim Min(1, IndCols)
Dim XtranspX() As Variant
Dim invXtranspX() As Variant
Dim beta As Variant

For j = 1 To IndCols
sum = 0
sumT = 50000000

    For i = 1 To IndRows

        If independent(i, j) = "" Then
        sum = sum
        Else
        sum = sum + 1
        End If
        Min(1, j) = sum
    Next i

Next j
23

n = Application.Min(Min)
k = IndCols + 1


ReDim X(n, k) As Double, XTransp(k, n) As Double, Y(n, 1) As Double
ReDim XtranspX(k, k), invXtranspX(k, k), xTranspY(k, 1) As Double
ReDim beta(k, 1), betaTransp(1, k) As Double
ReDim Ytransp(1, n) As Double
ReDim betaVar(k, k) As Double, SEBeta(k) As Double
    
Dim YTranspY As Double, betaTranspXTranspY As Double
Dim RSS As Double 'Residual Sum of Square
Dim TSS As Double 'total sum of squares
Dim ESS As Double 'residual sum of squares
Dim RMS As Double 'Sq[Standard error of Est Y] - residual variance
Dim meanY As Double, rSq As Double, sumY As Double, adjRSq As Double
Dim fStat As Double
    

    
'** Read in data - assign array and compute mean
    sumY = 0
    For i = 1 To n
        For j = 1 To k - 1                  'K-1 independent variables
            X(i, j + 1) = independent(i, j)
            
        Next j
        X(i, 1) = 1
        Y(i, 1) = Dependent(i, 1)
        
        sumY = sumY + Y(i, 1)
    Next i
    meanY = sumY / n
    
'** Transpose X and Y arrays - X' and Y'
    For i = 1 To k
        For j = 1 To n
            XTransp(i, j) = X(j, i)
            Ytransp(1, j) = Y(j, 1)
        Next j
    Next i
    
'** Compute X'X
    For i = 1 To k
        For j = 1 To k
            XtranspX(i, j) =
Application.Index((Application.MMult(XTransp, X)), i, j)   'X'X
        Next j
    Next i









'** Compute INV(X'X)
    
   For i = 1 To k
         For j = 1 To k
        invXtranspX(i, j) =
Application.Index((Application.MInverse(XtranspX)), i, j)   'INV(X'X)
         Next j
    Next i