Variable array format

Question

How can I write an array with variable sizing.

E.G. Linest(A2:A10) where "10" can come from INDEX(C3, B2:B50)

Answer 1

Use INDEX (or another function) to build the row number, then wrap it in INDIRECT (or OFFSET) to create a variable range for LINEST.

A typical pattern is:

=LINEST(A2:INDEX(A:A, C3))

Here, C3 contains the last row number (10 in the example), so A2:INDEX(A:A, C3) evaluates to A2:A10. This works directly in LINEST without needing an array constant.

If the last row number is calculated from a range like B2:B50, put that result in a cell (for example in C3), then use the same pattern:

=LINEST(A2:INDEX(A:A, C3))

If a dynamic array constant is needed (for example, to multiply the LINEST result by {n,1} as in the example), the constant itself must still be typed explicitly, but the data range passed to LINEST can be variable as shown above.

Remember that LINEST is an array function. In current Microsoft 365 versions, enter it normally and it spills. In earlier versions, select the output range and confirm with Ctrl+Shift+Enter.

---

References:

• LINEST function
• Create an array formula
• Overview of formulas