LINEST using only some of the values in an array

[Guest]

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?

 

[Bernard Liengme]

Why not sort the XY table (or a copy of it) and use LINEST on the required
section of the sorted data?
best wishes

 

[Guest]

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...