View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_] Harlan Grove[_2_] is offline
external usenet poster
 
Posts: 1,231
Default LINEST using only some of the values in an array

Incoherent wrote...
I want to perform a 4th order curve fit on some values in an array.
I have a list of X values and a list of Y values, the X values are
not in any particular order and there are in some cases multiple
instances of a given X value. I want to make a curve fit on the Y
data only when the corresponding X values are between some set
limits, set in seperate cells.


Mathematically incoherent. If you had multiple X values between bounds
A and B, you're going to pull all the X values if you use criteria A
<= X <= B. And if you have duplicate X values, you can't use LINEST.

If you mean you have some other variable (like row number) you could
use to isolate subsets of your X values that would all be distinct,
then you could use something like

=LINEST(N(OFFSET(Y,LARGE((A<=ROW(X))*(ROW(X)<=B)*( ROW(X)-MIN(ROW(X))
+1),
ROW(INDEX(1:65536,1,1):INDEX(1:65536,B-A+1,1)))-1,0,1,1)),
N(OFFSET(X,LARGE((A<=ROW(X))*(ROW(X)<=B)*(ROW(X)-MIN(ROW(X))+1),
ROW(INDEX(1:65536,1,1):INDEX(1:65536,B-A+1,1)))-1,0,1,1))^{0,1,2,3,4})