×
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!

*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

Excel Curve Fit Coefficients
4

Excel Curve Fit Coefficients

Excel Curve Fit Coefficients

(OP)
How do I access the curve fit coefficients?  For example, I want to curve fit a 2nd order polynomial to a set of data and display the coefficients in separate cells then evaluate the curve fit at a defined X value (say cell B5); i.e. y = A5*B5^2 + A6*B5 + A7.  So how do I extract the coefficients without copy and paste and get them in column A?

Next time around I drag and drop in new data and my new Y value is immediately calculated.  And I want to do this without a macro.  Hopefully I was able to sufficiently describe this.

RE: Excel Curve Fit Coefficients

See the LOGEST() function in excel help.

RE: Excel Curve Fit Coefficients

3
Check thread770-179377: Excel Curve Fit Lookup

These equations assume that your sheet has two named ranges: x and y.

Linear Trendline
Equation: y = m * x + b
m: =SLOPE(y,x)
b: =INTERCEPT(y,x)

Logarithmic Trendline
Equation: y = (c * LN(x)) - b
c: =INDEX(LINEST(y,LN(x)),1)
b: =INDEX(LINEST(y,LN(x)),1,2)

Power Trendline
Equation: y=c*x^b
c: =EXP(INDEX(LINEST(LN(y),LN(x),,),1,2))
b: =INDEX(LINEST(LN(y),LN(x),,),1)

Exponential Trendline
Equation: y = c *e ^(b * x)
c: =EXP(INDEX(LINEST(LN(y),x),1,2))
b: =INDEX(LINEST(LN(y),x),1)

2nd Order Polynomial Trendline
Equation: y = (c2 * x^2) + (c1 * x ^1) + b
c2: =INDEX(LINEST(y,x^{1,2}),1)
C1: =INDEX(LINEST(y,x^{1,2}),1,2)
b = =INDEX(LINEST(y,x^{1,2}),1,3)

3rd Order Polynomial Trendline
Equation: y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b
c3: =INDEX(LINEST(y,x^{1,2,3}),1)
c2: =INDEX(LINEST(y,x^{1,2,3}),1,2)
C1: =INDEX(LINEST(y,x^{1,2,3}),1,3)
b: =INDEX(LINEST(y,x^{1,2,3}),1,4)

Higher Order Polynomial Trendline
Notice the pattern in the two preceding sets of formulas

RE: Excel Curve Fit Coefficients

Great method.

You can obtain some statistical analysis data as well, to see how good your fit is. For example, the R2 value in case of a 2nd order polynomial:
r^2: =INDEX(LINEST(y,x^{1,2},TRUE,TRUE),3)
or in case of a log trendline:
r2: =INDEX(LINEST(y,LN(x),TRUE,TRUE),3)

If you have a more complex function, one approach is to estimate the coefficients, and compute "fitted" y-values to each x value, then compute the sum of squared errors. You can then use the Solver addin to minimize the sum of squared errors using the coefficients as "changing cells".

Cheers,
Joerd

Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

RE: Excel Curve Fit Coefficients

I cheat and double click on the graphed line and select the curve fit I want (ln, polynomial).  Excel the puts the equation on the graph and I cut and paste the equation to a cell (you have to remove "y=" with just "=".  

RE: Excel Curve Fit Coefficients

You can also get the coefficients and statistical data into cells by highlighting a range (see Excel Help on LINEST or LOGEST) type the =LINEST() function in the edit line and press Ctrl+Shift+Enter to enter the function as an array.  That way you don't have to use the INDEX function to get at the individual values.
  

RE: Excel Curve Fit Coefficients

that's true, but you'll get the output in Excel's pre-defined arrangement (i.e. coefficients in the first row, std.dev. in the next, etc.) The index formula is more flexible. It depends on what you want to do, and what you want your output to look like. The main drawback of Clyde's formulas is the repeated call to LINEST (3 or 4 times) where one time would be sufficient if you go with the standard output.

Cheers,
Joerd

Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

RE: Excel Curve Fit Coefficients

There appears to be a bug in Excel2004 for MacOSX, 11.3.5 (at least). Here's an example of 2nd order polynomial fit. If I use the format
linest(yarray,xarray^{1,2},1,1)
 the results are garbage.  If I create two columns, one with x values and the next with x^2 values, then
linest(yarray, x:x2,1,1) works fine (assuming I've named the two columns "x" and "x2" ) .
The exact same spreadsheet seemed to produce the correct result using the first format when run under ExcelX for MacOSX (the older version of Office).  So, there seems to be a problem under Excel2004 in expanding that power-array part of the formula.

Can anyone verify this, and does anyone know how I can contact a "useful" person at Microsoft to track this down?

RE: Excel Curve Fit Coefficients

Followup:
Several people on various microsoft/excel forums have told me this is a known bug in Excel2004 (OSX).  The workaround is to use the transpose:  (note the semicolon in there)

=LINEST(TRANSPOSE(y_range),TRANSPOSE(x_range)^{1;2},TRUE,TRUE)

The problem appears to be the way that xarray^{1,2} is returned to the
function.

Please report it again to MacBU (via XL's Help/Send Feedback). It may
not be too late to get it fixed in XL08.

RE: Excel Curve Fit Coefficients

cub3bead:
You're absolutely correct - I've used CurveExpert for several years now.
An even better (more powerful) tool is Regress+, by Mike McLaughlin.  However, that's MacOS only, which some of us regard as a bonus smile .

I fully agree with the rule that Excel should be the platform of last resort for just about anything that involves math.

RE: Excel Curve Fit Coefficients

Having trouble getting this to work for a 3rd degree curve. It keeps wanting to make c3 = 0 and then c2 and c1 are equal to c1 and c2 from a 2nd degree regression.  I've checked the formulas like 10x so that is not the problem. I'm using Excel 2003, not on a mac. any ideas?

c3: =INDEX(LINEST(y,x^{1,2,3}),1)

RE: Excel Curve Fit Coefficients

Koether:
Have you tried dumping the complete LINEST output, i.e. selecting a 4columnx5row region and using shift-ctrl-return to get all the coefficients and params?
And just for completeness' sake, try using the
'transpose' version posted above to see what you get.  
I just ran a linest(transpose(y),transpose(x^{1;2;3}) and got appropriate results (OSX Excel2004).

If that doesn't help, can you post your spreadsheet/data somewhere for me to try out?

Carl

RE: Excel Curve Fit Coefficients

I think I partly figured out what is going on, the macro that imports the data time stamps it, and with the excel time format I think it is causing some sort of overrun/rounding error.  If I recalc the X values in terms of seconds from the first reading, (excel time format is decimal days.) I get a value for c3 and it follows the polynomial trend generated by excel in the graph.  I can post up the sheet somewhere if you guys are curious.

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! Already a Member? Login


Resources

Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now
The Great Project Profitability Debate
A/E firms have a great opportunity to lead the world into the future, but the industry’s greatest asset—real-time data—is sitting wasted in clunky, archaic ERP platforms. Learn how real-time, fully interactive dashboards in a modern ERP allow you to unlock data that will shape the future of the world. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close