Using arrays in LINEST for multi variable regressions

[bevercam]

Is there a way to combine two columns into a single array such that it can be used inside the LINEST function?

for example, LINEST(known_ys, known_xs, const, stats) where the known_xs are two different columns of the same length from two different tabs, as opposed to be adjoining columns

 

[Domenic]

We can use a custom function to combine multiple ranges into an array. First, copy and paste the following custom function into a regular module (Visual Basic Editor [Alt+F11] >> Insert >> Module)...

Option Explicit

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003

    Dim TempUnion() As Variant
    Dim i As Long, Itm As Variant, Ctr As Long
  
    For i = LBound(Arg) To UBound(Arg)
        Arg(i) = Arg(i)
        If IsArray(Arg(i)) Then
            For Each Itm In Arg(i)
                Ctr = Ctr + 1
                ReDim Preserve TempUnion(1 To Ctr) As Variant
                TempUnion(Ctr) = Itm
            Next Itm
        Else
            Ctr = Ctr + 1
            ReDim Preserve TempUnion(1 To Ctr) As Variant
            TempUnion(Ctr) = Arg(i)
        End If
    Next i
  
    ArrayUnion = TempUnion

End Function

Then, you can use the following worksheet formula, as an example...

=LINEST(ArrayUnion(Sheet1!A2:A5,Sheet2!A2:A5),ArrayUnion(Sheet1!B2:B5,Sheet2!B2:B5),,TRUE)

Change the sheet names and ranges, accordingly.

Hope this helps!