LINEST using only some of the values in an array
 

Try setting the constant term to 1 and multiplying by a vector of 0's
and 1's:

=LINEST(Y10:Y100,(X10:X100$A$1)*(X10:X100<$B
$1)*IF(X10:X100,X10:X100^{0,1,2,3,4},1),1)

Note: LINEST(y,X,1) can be written in matrix terms [Inv(X'X)]X'y so
inserting extra lines of 0's into the X matrix has no effect on the
least squares estimates regardless of the y values.


On 23 May, 11:07, 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.

I have tried (where my limits are $A$1 and $B$1):

{LINEST(IF(X10:X100$A$1,IF(X10:X100<$B$1,Y10:Y100 )),IF(X10:X100$A$1,IF(X1*0:X100<$B$1,X10:X100^{1, 2,3,4})))}

This gives #VALUE if the limits do not encompass the whole data X range. I
guess because this results in non contiguous data in the array...

Any smart ways around this?