Finding an equation for a curve in Excel?

[schnell]

Hi,

I have some data points that form a curve.

I wish to put the points into excel and get it to put an equation to the line.

Do you know what is th name of the Excel math software feature that does this...so that i can install it.

Also, do you know how i can go about this?

 

[Onda]

I usually use this software:

http://www.cadreanalytic.com/Regress.htm

it is free and is better than Excel.

Onda

 

[MintJulep]

If you plot the points on an Excel chart you can use the "Add Trend Line" feature to display the equation on the chart.

If you just want the coefficients you can use the LINEST() or LOGEST() functions. They are array functions, so read help carefully.

 

[corus]

I use CurveExpert 1.4. It's also free.

Tata but not yet tara

 

[schnell]

Hi,

w tried excel and cadreanalytic and bith didn't work when we selected cubic polynomial.

Excel showed us a graph that was a near perfect fit............it also gave us the equation....but when we plotted it ourselves it did not agree with the excel function.

-do you know what we can do?

(our data points were almost a straight line but with a bit of a wiggle , so we tried cubic.)

 

[Onda]

The problem of excel is that don't show you all significant digit of the formula. So you can see a very good fitting curve but when you try to replicate it doesn't work.
I don't know why regress don't work for you.
I've used a lot with always good result.

 

[IRstuff]

Post a sheet?

TTFN

FAQ731-376
Chinese prisoner wins Nobel Peace Prize

 

[corus]

You can change the format of the printed equation so it has more decimal places or scientific notation. That should give you a better fit.

Tata but not yet tara

 

[Onda]

How to change the format of printed equation?
thanks

 

[Onda]

I've found. Right click on the equation and change the format.
thanks anyway.

 

[IDS]

The Excel help gives the formula below for fitting a cubic curve to a set of x,y data:

=LINEST(yvalues, xvalues^COLUMN($A:$C))

I prefer:

=LINEST(yvalues, xvalues^{1,2,3}) (note curly brackets)

but they give the same answer, and the same as the trend line in a chart for the same data.

To get all 4 coefficients enter the formula as shown, select the cell with the formula and the adjacent three cells to the right, press F2, then ctrl-shift-enter.

You can also use the INDEX() function to return a coefficient other than the first one as a single value. e.g.:

=INDEX(LINEST(yvalues,xvalues^{1,2,3}),2)

to return the coefficient for x^2



Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[schnell]

Hi,

These are the Excel graphs.....

http://i51.tinypic.com/1y0ht0.jpg

....thin black line = from our data.
....thick black line is what excel plotted
....pink line is our plot of the equation that excel
claims represents the thick black line

(as you can see, the thick black line and pink lines don't match.)

Our data is as follows......

X Y
820 1036
755 904
715 851
696 818
644 748
501 528
370 370

 

[schnell]

OK thanks Corus and Onda and all other repliers..


we did the decimal place thing on the formula and its ok now.

thankyou to all.

 

[Onda]

With normal precision (4 digits) I've the same problem but if I increase the precision of the formula to 8 digit my plot of the equation exactly match the excel plot of interpolation.
It's just a problem of digits!!
Regards

 

[IDS]

Results from Linest attached. (see edit box for the formula)



Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[corus]

Out of interest, CurveExpert gives the much simpler fit to the data for a Saturation Growth-Rate Model: y=ax/(b+x) with
a = -2.18445071972E+003
b = -2.55488142333E+003


Tata but not yet tara

 

[BigInch]

Looks like this reference works here too,

http://www.eng-tips.com/viewthread.cfm?qid=214997&page=1