LINEST with dynamic range using OFFSET won't spill

nbrophywilliams

New Member
Joined
Apr 9, 2015
Messages
18
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
    Annotation 2020-02-24 162924.jpg
    42.4 KB · Views: 18

Some videos you may like

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
Joined
Dec 9, 2003
Messages
1,077
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top