×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Finding an equation for a curve in Excel?

Finding an equation for a curve in Excel?

Finding an equation for a curve in Excel?

(OP)
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?

RE: Finding an equation for a curve in Excel?

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.  

RE: Finding an equation for a curve in Excel?

I use CurveExpert 1.4. It's also free.

Tata but not yet tara

RE: Finding an equation for a curve in Excel?

(OP)
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.)

RE: Finding an equation for a curve in Excel?

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.

RE: Finding an equation for a curve in Excel?

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

RE: Finding an equation for a curve in Excel?

How to change the format of printed equation?
thanks

RE: Finding an equation for a curve in Excel?

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

RE: Finding an equation for a curve in Excel?

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/
 

RE: Finding an equation for a curve in Excel?

(OP)
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


 

RE: Finding an equation for a curve in Excel?

(OP)
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.

RE: Finding an equation for a curve in Excel?

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

RE: Finding an equation for a curve in Excel?

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources