# LINEST with dynamic range using OFFSET won't spill

#### nbrophywilliams

##### New Member
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!

#### Attachments

• Annotation 2020-02-24 162924.jpg
42.4 KB · Views: 18

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### KRice

##### Well-known Member
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

##### New Member
Solved it! Thanks so much.

Replies
8
Views
149
Replies
3
Views
123
Replies
2
Views
137
Replies
1
Views
134
Replies
3
Views
70

1,128,017
Messages
5,628,163
Members
416,296
Latest member
smartua

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back