Consider the excel function LINEST(B$3:B$18, $A$3:$A$18^{1,2,3,4,5}). How can we replace B$3:B$18 and $A$3:$A$18 with variables?

Question

Consider the excel function =LINEST(B$3:B$18, $A$3:$A$18^{1,2,3,4,5}). How can we replace B$3:B$18 and $A$3:$A$18 with variables?

I'm dealing with multiple arrays of variable sizes. Using XMATCH and ADDRESS, I can determine A1 style addresses of the element in the last row and column of each array. ($F10 for an array starting at $A$1, for instance.)

Whether I need VBA or not, how can I create a function allowing me to call the LINEST function above dynamically for arbitrary arrays?

I don't know how to begin to substitute the $A$3:$A$18 in the above with a variable that I create. Do I manually assign a range variable (ranvar for example) to each array and replace the $A$3:$A$18 in the LINEST call with ranvar?

Answer 1

If I understand correctly what you are trying to achieve, you could do so by using the INDIRECT function.

Cells E1 and F1 have the following formulas:

="B3:B"&COUNT(B:B)+2
="C3:C"&COUNT(C:C)+2

Which return the ranges of the input date and the LINEST function uses `INDIRECT':

=LINEST(INDIRECT(E1),INDIRECT(F1)^{1,2,3,4,5})

Of course you could almost do it in a single function:

=LINEST(
INDIRECT("B3:B"&COUNT(B:B)+2),
INDIRECT("C3:C"&COUNT(C:C)+2)^{1,2,3,4,5})

Or if you are feeling particularly fancy:

=LET(
    numRows, COUNTA(B:B),
    yRange, INDEX(B:B, 3):INDEX(B:B, numRows + 2),
    xRange, INDEX(C:C, 3):INDEX(C:C, numRows + 2),
    powers, SEQUENCE(1, 5, 1, 1),
    LINEST(yRange, xRange ^ powers))

You also use named ranges as your inputs.