Help with LINEST function-Defining the x y value ranges

[loneRANGEr]

First off, thank you for the terrific resource for excel help. Hopefully this is an easy problem to solve for those who are not excel illiterate like myself.


I have two columns of data that I plot as an x-y scatter plot and then (after examining the graph) I want to select a portion of that data to fit with the LINEST function. Actually, I typically have upwards of 50 of the pairs of x-y values to evaluate at time, so I'd like to find an easier way to select the x,y ranges besides manually going into the formula bar to change the ranges (which are different for each case). I've experimented with inserting form controls so I can easily select starting and ending cell references to the x-y data, but how can I link that information back to the ranges in the LINEST formula? Any help would be greatly appreciated and I'd be happy to try to explain more if necessary. Thanks again.

 

[Hui]

LoneRanger


Firstly, Welcome to the Chandoo.org Forums


You can add an Offset function into the Linest function to allow you to quickly vary the starting location as well as the length of the sub-range


Assuming that your data is Columnar and X data is in Column A2:Ax and Y data is in Column B2:Bx


I have also used two Named Ranges

Start: The offset from A2 & B2 to the Top of your sub-range

Height: The length of your sub-range


Then just use Linest as below:

=LINEST(OFFSET(B1,Start,,Height),OFFSET(A1,Start,,Height),TRUE,TRUE)

 

[loneRANGEr]

Hi Hui,


Thank you so much for your help....your solution works perfectly.