LINEST using only some of the values in an array
 

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(X10: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?

LINEST using only some of the values in an array
 

Why not sort the XY table (or a copy of it) and use LINEST on the required
section of the sorted data?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Incoherent" wrote in message
...
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(X10: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?

LINEST using only some of the values in an array
 

Thanks for the reply

Ideally I would like to have the sheet automatically update since it is a
template of sorts.
The reason for this is that, while the original values of X are constant,
they are "wrapped" via a MODULUS operation in order to display the data as a
function of various periods. Since this period changes (along with the
offset) depending on what one wishes to see, (actually in two axes, the data
is 3 dimensional) manually sorting, or even using a Pivot plus refresh to
sort it, is a little cumbersome

Big picture; what I am really trying to do is smooth Z data as a function of
scattered X and Y. This method I have used successfully with data which is
place on constant coordinate pitches in X and Y, but when coordinates are
random, (but known) it is proving a little more difficult. There are
solutions using Addins (some very good) but I would like to keep it as
"simple" as possible. I have a clumsy solution using sorted lookup tables but
I like to minimise the number of columns used...

"Bernard Liengme" wrote:

Why not sort the XY table (or a copy of it) and use LINEST on the required
section of the sorted data?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Incoherent" wrote in message
...
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(X10: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?