LINEST with dynamic range using OFFSET won't spill

[nbrophywilliams]

Hello Mr Excel users,
I'm having trouble fitting a polynomial trendline to my dataset. The data contains a dynamic range, so I am using the OFFSET function to adjust the range used in the LINEST formula, with the hope of producing a 3rd order polynomial.
The current syntax involves an array formula that looks like this
Resultant cell {=LINEST(OFFSET(Q45,N47,0):Q45,OFFSET(N45,N47,0):N45{1,2,3})}
where Q45 and N45 are a constant end to the two ranges,
N47 is a number reflecting how many rows up (it's a negative number) that the array should take into account for the calculation. It's essentially a count of how many rows include data (not #N/A).

Unfortunately my work internet has blocked me from downloading the relevant add-ins so I can't share a screenshot, so I hope I've outlined my problem clearly.

I'm fairly sure I've made some sort of error as running the array does NOT result in the formula spilling across four cells (ideally it would give me x^3, x^2, x + c).

Any direction is much appreciated!

 

[KRice]

Try this small correction to your formula:
=LINEST(OFFSET(Q45,N47,0):Q45,OFFSET(N45,N47,0):N45^{1,2,3})

Note the syntax to raise the x values to powers of 1, 2, and 3.
This formula is entered in a cell as written. Then select the cell and highlight the next three cells to the right (so that four cells are highlighted), hit F2, and then use CTRL-SHIFT-ENTER to enter as an array formula. Coefficients for x^3, x^2, x^1, and x^0 should be displayed, and changing the value in N47 should result in selecting a subset of the data.

 

[nbrophywilliams]

Solved it! Thanks so much.