Excel Excel Curve Fit Coefficients (thread770-184726)
Excel Excel Curve Fit Coefficients (thread770-184726)
(OP)
To get horizontal/vertical (example) line touch at a user-defined point on a linear curved line (not trendline), I've tried:
Equation: y = m * x + b
m: =SLOPE(y,x)
b: =INTERCEPT(y,x)
and
=INDEX(LINEST,y,x,cond)
on y,x ranges -- see attached file
but the only way I can get it to work is select the y,x values immediately above & below the user-defined point.
Can anyone help me out on this, pls?
Equation: y = m * x + b
m: =SLOPE(y,x)
b: =INTERCEPT(y,x)
and
=INDEX(LINEST,y,x,cond)
on y,x ranges -- see attached file
but the only way I can get it to work is select the y,x values immediately above & below the user-defined point.
Can anyone help me out on this, pls?





RE: Excel Excel Curve Fit Coefficients (thread770-184726)
The only other options if the data is representing a curve and not a straight line is by adding a trendline, but making it a polynomial:
@ 2 orders: y = 0.0007x^2 + 0.0905x + 0.4346
@ 3 orders: y = 2e-6x^3 + 0.0001x^2 + 0.1459x - 1.4783
@ 4 orders: y = 2e-8x^4 -6e-6x^3 +0.0013x^2 +0.0654x + 0.6004
RE: Excel Excel Curve Fit Coefficients (thread770-184726)
RE: Excel Excel Curve Fit Coefficients (thread770-184726)
The "correct" chart is correct because I selected x,y values directly above & below the user-selected value (for crossing point on chart). If I select the whole range, so the user can choose any point within the range for the crossing point on chart, it does not work (see "wrong" chart).
I added polynominal trendlines to both charts, and it is still wrong on "wrong" chart. So I tried to apply your instructions
@ 2 orders: y = 0.0007x^2 + 0.0905x + 0.4346
@ 3 orders: y = 2e-6x^3 + 0.0001x^2 + 0.1459x - 1.4783
@ 4 orders: y = 2e-8x^4 -6e-6x^3 +0.0013x^2 +0.0654x + 0.6004
but have not understood how or from where these values are obtained. Are they specific to this data range or are they constants? I underline the obvious: This is first time I have seen or tried to use INDEX(LINEST.
Thanks again for any help
RE: Excel Excel Curve Fit Coefficients (thread770-184726)
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Excel Excel Curve Fit Coefficients (thread770-184726)
Naturally a line of overall best fit won't be the same as a line of best fit for a few points from that data.
In general, if you want to interpolate the data to get a best value at a given point then you are better to interpolate from local points, rather than a global line of best fit for the whole data. Better still would be to use a quadratic best fit that used several points local to your data. Excel will do that if you select 3 ro 4 points to graph.
corus