How to apply LinEst function to rows?

Question

I've been using the WorksheetFunction.LinEst to do quadratic regression for years without problems. My data has always been stored in columns in the Excel Worksheet.

Now I'm being sent data in rows, rather than columns. My calls to WorksheetFunction.LinEst are failing.
If I process the same command as a formula in the worksheet, it works.

I don't have the option to transpose the data. I'm using the most recent release versions of Windows 10 and Microsoft Office 365.

I couldn't find any examples on here written in VBA that had the data stored in rows.

Here's a clean copy of my subroutine that I call to perform the regression. I've removed all the debugging code, to make it more readable.
The full version is farther down.
Following this code is some code I wrote to demonstrate the failure.

Sub GetPolynomialRegressionCoefficients(Xs As Excel.Range, Ys As Excel.Range, ByRef x1 As Double, ByRef x2 As Double, ByRef x3 As Double)
    '
    ' Calculates the best fit cooeficients of the the data stored in ranges Xs and Ys
    '
    Dim rgCoeff  ' This will be a variant array of the coefficients calculated for the best fit quadratic curve
    
    rgCoeff = Application.WorksheetFunction.LinEst(Ys, Application.Power(Xs, Array(1, 2)))
    
    x1 = rgCoeff(1)
    x2 = rgCoeff(2)
    x3 = rgCoeff(3)
End Sub

This next code creates a simple dataset to calculate the coefficients for the y = x^2 function. Using the same data, first stored in columns, and then stored in rows, my code works with the data in columns, but fails with the data in rows.

Sub TestGetPolynomialRegressionCoefficients()
    Dim rXs As Excel.Range  ' Range for the X values
    Dim rYs As Excel.Range  ' Range for the Y values
    Dim ws As Excel.Worksheet
    Dim iRow As Long
    Dim iCol As Long
    Dim x As Long
    Dim x1 As Double
    Dim x2 As Double
    Dim x3 As Double
    
    Set ws = ThisWorkbook.Worksheets("LinEstTest")
    '
    ' Works! - Test data y = x^2 with data in columns
    '
    ws.Cells.Clear
    For x = 0 To 9
        iRow = x + 1
        ws.Cells(iRow, 1) = x         ' these will be the domain (the Xs)
        ws.Cells(iRow, 2) = x * x     ' these will be the range (the Ys)
    Next x
    
    Set rXs = ws.Range(ws.Cells(1, 1), ws.Cells(10, 1))
    Set rYs = ws.Range(ws.Cells(1, 2), ws.Cells(10, 2))
    
    On Error Resume Next
    x1 = -1: x2 = -1: x3 = -1
    GetPolynomialRegressionCoefficients rXs, rYs, x1, x2, x3
    If Err <> 0 Then
        Debug.Print "Error using Columns "; Err; " "; Err.Description
    Else
        Debug.Print "With data in columns, x1 = "; x1; ", x2 = "; x2; ", x3 = "; x3
    End If
    '
    ' Fails! - Test data y = x^2 with data in rows
    '
    ws.Cells.Clear
    For x = 0 To 9
        iCol = x + 1
        ws.Cells(1, iCol) = x         ' these will be the domain (the Xs)
        ws.Cells(2, iCol) = x * x     ' these will be the range (the Ys)
    Next x
    
    Set rXs = ws.Range(ws.Cells(1, 1), ws.Cells(1, 10))
    Set rYs = ws.Range(ws.Cells(2, 1), ws.Cells(2, 10))
    
    On Error Resume Next
    x1 = -1: x2 = -1: x3 = -1
    GetPolynomialRegressionCoefficients rXs, rYs, x1, x2, x3
    '
    ' Get Error message dialog:
    '
    ' Microsoft Visual Basic
    ' Run-time error '1004':
    ' Unable to get the LinEst property of the WorksheetFunction class
    '
    If Err <> 0 Then
        Debug.Print "Error Using Rows "; Err; " "; Err.Description
    Else
        Debug.Print "With data in rows, x1 = "; x1; ", x2 = "; x2; ", x3 = "; x3
    End If
End Sub

Here's the output I get in my immediate window when I run the test code:

With data in columns, x1 =  1 , x2 =  0 , x3 =  0 
Error Using Rows  1004  Unable to get the LinEst property of the WorksheetFunction class

Finally, here's the full version of my routine with debugging and validation code. Provided for reference only (please don't critique it):

Sub GetPolynomialRegressionCoefficients(Xs As Excel.Range, Ys As Excel.Range, ByRef x1 As Double, ByRef x2 As Double, ByRef x3 As Double)
    '
    ' Calculates the best fit cooeficients of the the data stored in ranges Xs and Ys
    '
    Dim rgCoeff  ' This will be a variant array of the coefficients calculated for the best fit quadratic curve
#If RELEASE = 0 Then
    Dim iRow As Long  ' Used only for debugging purposes.
    Dim iCol As Long  ' Used only for debugging purposes.
    '
    ' Confirm that the ranges are the same size.
    '
    If (Xs.Rows.Count <> Ys.Rows.Count) And (Xs.Columns.Count <> Ys.Columns.Count) Then Stop
    '
    ' Confirm that all the data in the ranges is numeric and not blank
    '
    For iRow = 1 To Ys.Rows.Count
        For iCol = 1 To Xs.Columns.Count
            If IsNumeric(Xs.Cells(iRow, iCol)) = False Or IsNumeric(Ys.Cells(iRow, iCol)) = False Or Trim(Xs.Cells(iRow, iCol)) = "" Or Trim(Ys.Cells(iRow, iCol)) = "" Then Stop
        Next iCol
    Next iRow
            
    DoEvents
#End If
    
    rgCoeff = Application.WorksheetFunction.LinEst(Ys, Application.Power(Xs, Array(1, 2)))
    
    x1 = rgCoeff(1)
    x2 = rgCoeff(2)
    x3 = rgCoeff(3)

End Sub

user_5958842 · Answer

TLDR: For data in rows, you need to use Array(Array(1), Array(2)) instead of Array(1, 2)

The problem is not the WorksheetFunction.LinEst function but the Application.Power function. To check this, you can add an intermediary variable called XsArray like this:

Sub GetPolynomialRegressionCoefficients(Xs As Excel.Range, Ys As Excel.Range, ByRef x1 As Double, ByRef x2 As Double, ByRef x3 As Double)
    '
    ' Calculates the best fit coefficients of the data stored in ranges Xs and Ys
    '
    Dim rgCoeff  ' This will be a variant array of the coefficients calculated for the best fit quadratic curve
    
    Dim XsArray As Variant
    XsArray = Application.Power(Xs, Array(1, 2))

    rgCoeff = Application.WorksheetFunction.LinEst(Ys, XsArray)
    
    x1 = rgCoeff(1)
    x2 = rgCoeff(2)
    x3 = rgCoeff(3)
End Sub

And if you open the Local Window (after putting a breakpoint), you'll see that this is where the error comes from:

I couldn't find any good existing explanations about this but the way I understand it is that the Power function kinda works like matrix multiplication : you either want to have a row matrix multiplying a column matrix or vice-versa, you don't want two row matrices or 2 column matrices.

The thing here is that Array(1,2) is seen by VBA as a row Matrix since it is a simple 1D array. So, everything is fine when Xs is a "column range", but when it's a "row range", we need to pass something that will be seen as a column matrix. One way to achieve this would be like this:

Sub GetPolynomialRegressionCoefficients(Xs As Excel.Range, Ys As Excel.Range, ByRef x1 As Double, ByRef x2 As Double, ByRef x3 As Double)
    '
    ' Calculates the best fit coefficients of the data stored in ranges Xs and Ys
    '
    Dim rgCoeff  ' This will be a variant array of the coefficients calculated for the best fit quadratic curve
    
    Dim XsArray As Variant
    If Xs.Rows.Count > Xs.Columns.Count Then
        XsArray = Application.Power(Xs, Array(1, 2))
    Else
        XsArray = Application.Power(Xs, Array(Array(1), Array(2)))
    End If

    rgCoeff = Application.WorksheetFunction.LinEst(Ys, XsArray)
    
    x1 = rgCoeff(1)
    x2 = rgCoeff(2)
    x3 = rgCoeff(3)
End Sub

Explanation

The expression Array(Array(1), Array(2)) returns a jagged array but, from what I understand, since it requires 2 indexes to return an element, VBA will interpret it similarly as a 2D array and these indexes will be seen as coordinates from a (column) matrix: (0,0) and (1,0).

Alternatively

If you don't like jagged arrays, you could always create a real 2D array with a loop:

Dim XsArray As Variant, PowersArray As Variant

If Xs.Rows.Count > Xs.Columns.Count Then
    PowersArray = Array(1, 2)
    XsArray = Application.Power(Xs, PowersArray)
Else
    ReDim PowersArray(0 To 1, 0)
    Dim i As Integer
    For i = 0 To 1
        PowersArray(i, 0) = i + 1
    Next i
    XsArray = Application.Power(Xs, PowersArray)
End If

How to apply LinEst function to rows?

1 Answers