ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   LINEST using only some of the values in an array (https://www.excelbanter.com/excel-worksheet-functions/143884-re-linest-using-only-some-values-array.html)

Harlan Grove[_2_]

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})



All times are GMT +1. The time now is 01:45 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com