Excel Excel Curve Fit Coefficients (thread770-184726)

[bergspyder]

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?

 

[zelgar]

I'm not certain that what you're trying to do is correct. Using the data in your spreadsheet, a linear trendline gives the same data as using the slope & intercept or using the linest commands.

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

 

[zelgar]

Also, with adding the trendline you have the option to set the Y intercept. The above formulae did not force the Y intercept to be equal to 0.

 

[bergspyder]

Thanks for reply Zelgar but I'm still looking for help, sorry, probably because I haven't explained problem accurately or clearly. Therefore I attach file showing the specific problem. There are 2 charts both with curves from same data (see data!). One is correct, the other is wrong.

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

 

[IRstuff]

If you don't have straight line, why would you expect a linear fit to match?

TTFN

FAQ731-376

 

[corus]

I think you are mixing two things up here. A linear regression through the whole data will give you a line of best fit for all of the data. Basically the line minimises the error between predicted and actual values. You are then trying to compare this line with a line that only goes through points adjacent to the position you are interested in.
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