Smart questions
Smart people
 Find A ForumFind An Expert
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Remember Me

Are you an
Engineering professional?
Join Eng-Tips now!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• 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.

Just copy and paste the

#### Feedback

"...I've gotten solutions within a day - it saved a lot of time and actually got me one ATTABOY from my boss..."

#### Geography

Where in the world do Eng-Tips members come from?

# Excel Curve Fit Coefficients(3)

 Forum Search FAQs Links Jobs Whitepapers MVPs
 rnordquest (Aeronautics) 19 Apr 07 13:59
 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.
 MintJulep (Mechanical) 19 Apr 07 14:24
 See the LOGEST() function in excel help.
 (2)  Clyde38 (Electrical) 19 Apr 07 15:47
 Check thread770-179377: Excel Curve Fit LookupThese equations assume that your sheet has two named ranges: x and y.Linear TrendlineEquation: y = m * x + bm: =SLOPE(y,x)b: =INTERCEPT(y,x)Logarithmic TrendlineEquation: y = (c * LN(x)) - bc: =INDEX(LINEST(y,LN(x)),1)b: =INDEX(LINEST(y,LN(x)),1,2)Power TrendlineEquation: y=c*x^bc: =EXP(INDEX(LINEST(LN(y),LN(x),,),1,2))b: =INDEX(LINEST(LN(y),LN(x),,),1)Exponential TrendlineEquation: y = c *e ^(b * x)c: =EXP(INDEX(LINEST(LN(y),x),1,2))b: =INDEX(LINEST(LN(y),x),1)2nd Order Polynomial TrendlineEquation: y = (c2 * x^2) + (c1 * x ^1) + bc2: =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 TrendlineEquation: y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + bc3: =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 TrendlineNotice the pattern in the two preceding sets of formulas
 joerd (Chemical) 20 Apr 07 9:29
 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, JoerdPlease see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
 dcasto (Chemical) 20 Apr 07 14:50
 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 "=".
 jghrist (Electrical) 20 Apr 07 15:58
 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.
 joerd (Chemical) 23 Apr 07 9:36
 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, JoerdPlease see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
 TerryScan (Civil/Environmental) 26 Apr 07 12:12
 Check recent post: thread770-179377: Excel Curve Fit Lookup
 cellocgw (Aerospace) 18 Jun 07 8:27
 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, thenlinest(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?
 cellocgw (Aerospace) 19 Jun 07 8:40
 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 thefunction.Please report it again to MacBU (via XL's Help/Send Feedback). It maynot be too late to get it fixed in XL08.
 cub3bead (Chemical) 19 Jun 07 14:24
 Better solution is to download the sharware program CurveExpert from http://www.ebicom.net/~dhyams/cmain.htmIt is a fantastic piece of shareware.
 cellocgw (Aerospace) 19 Jun 07 14:49
 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 .I fully agree with the rule that Excel should be the platform of last resort for just about anything that involves math.
 Koether (Mechanical) 20 Jun 07 16:19
 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)
 cellocgw (Aerospace) 21 Jun 07 7:36
 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
 Koether (Mechanical) 21 Jun 07 10:44
 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.

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!