×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

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

Polynomial Trendline parameter Extraction

Polynomial Trendline parameter Extraction

Polynomial Trendline parameter Extraction

(OP)
Does anyone know of a way to extract the paramters from a polynomial regression (trendline) in Excel. I have quickly looked through the object model and don't see it.

RE: Polynomial Trendline parameter Extraction

I had tried to find how to extract the trendline equation to no avail. I believe we even called microsoft and they said it was all internal with nothing accessible. We ended up having to write a function that calculated the trendline, or you are left with displaying the equation and copying into cells manually.

RE: Polynomial Trendline parameter Extraction

all,

please look at thread:  Thread770-44555

determining the coefficients of a polynomial equation is determined by using the "LINEST" function.

i trust u have plotted data and incorporated the trendline capabilities, but how to obtain the coefficients is the question.

a technique is by the linest function.  although i've not readily notice this technique widely publisized, here it is.  this formula must be entered as an array formula, otherwise it will not work.

the standard linest function is as follows:
=linest(known y's, known x's, const, stats)

to determine a polynomial, array enter:
=linest(known y's, known x's^{1,2,3}, const, stats)

where this equation will determine a 3rd degree polynomial.
by array enter, i mean to select 4 rows x 4 columns, enter the formula, and then "ctrl"+"shift"+"enter".

there are web sites that provide better explanations to use array formulas.

if need be, i will gladly forward an example file to you demonstrating the two techniques.
-pmover

RE: Polynomial Trendline parameter Extraction

(OP)
Thank you ivanlok for the reply. I suspected as much. As for pmover, I respectfully submit that my question was with regards to polynomial, not linear regression. The linest function is applicable to linear regression only.

Thank you both for taking the time to respond.

RE: Polynomial Trendline parameter Extraction

RGCook,

i understood the question that you needed to determine the coefficients of a polynomial equation (i.e. A0 + A1x + A2x + ...= 0, where the coefficients are A0, A1, A2, An...), based on some x and y data.

i trust that the data was plotted and you've implemented a trendline, which is displayed on the chart.

the technique described does provide the same coefficients that a trendline provides, which is displayed on the chart.

sorry for any confusion!
good luck!
-pmover

RE: Polynomial Trendline parameter Extraction

Please be warned that teh polynomial tendline as displayed on the graph looks lovely (appears to fit the data).
Under chart options, the equation can be displayed.

However, the equation can be quite badly wrong, and we have not found the reason behind it. We find that using the displayed coefficients to calculate a polynomial on the same x axis data gives a completely different result
when plotted on the same graph!!!  No doubt it sometimes works but not i the value series we have been using.

Suggest you try your own matching technique  Others on the web have commented on this error also.

RE: Polynomial Trendline parameter Extraction

hazelwoodr,

You may want to check the discussion in the following thread in regards to improving the accuracy of the displayed equation for a trendline.  Increasing the number of significant digits that are displayed makes all the difference.  Makes you wonder why Excel displays anything less.
Thread770-44555

RE: Polynomial Trendline parameter Extraction

(OP)
Thanks for the warning and additional information. I have decided to write my own function that performs polynomial regression using least squares method. A benefit of this is that it does not have the limitation in terms of only going to the fifth order (as Excel limits).

That is not meant to imply that Excel is limited. Indeed, I have found that Excel uses a rather advanced routine that improves upon the result using least square. I guess that doesn't surprise me too much, they have had a while to work on it. What does surprise me, however, is that the parameters are not available...even via the object model using Visual Basic. I suppose it has something to do with the complexity of making all of this available to developers who would probably use a more sophisticated method if they in fact wanted this information in the first place. After all, they'd have to allow for an additional five parameters for up to fifth order regression. Or, perhaps developers over at MS are working on more important matter.

In any event, I am in the process of using linear algebra to solve the resulting matrices stemming from this endeavor. It is not something I have looked at in a while, and something I was hoping to avoid. It is an interesting trip back in time for me. I can almost see myself in class as I write this.

Again, thank you for your help.

RE: Polynomial Trendline parameter Extraction

Hazelwoodr,

I ran into the same problem.  I think the problem you're having may be due to the formatting of the equation.  The equation displayed for the trendline often has too few decimal places for the coefficients.  Reformatting the equation to show more decimal places should improve the accuracy of your calculated values.

RGCook,

Set the trendline options to display the equation for the trendline.  Start with the = sign and select the entire equation.  Copy and paste into the cell you want to calculate and replace the x's in the equation with the appropriate cell reference.  Be sure to check your calculated values against your data points to make sure you have enough decimal places in the coefficients.  One last point.  Be careful with higher order polynomial curve fits if you have to extrapolate from your data.  They can head off in odd directions outside your data set.

RE: Polynomial Trendline parameter Extraction

Dear RGCook

The method explained by PMOVER works exactly as he describes. Don't be too single-minded about the LINEST function. It normally does LINEAR regression but it does POLYNOMINAL regression when invoked as an array function. It's all explained by PMOVER.

As an example, if you need a third degree polynominal (such as y = ax³+bx²+cx+d), then select four cells in a row and enter the formula =linest(known-y's, known-x's^{1,2,3},1,) and hit control shift enter. This puts the coefficients a,b,c&d in the four selected cells. In the above formula, the "known-y's" should be replaced by the cells containing the known y values, same for known-x's.

Another example : If you need a second degree polynomial (such as y = ax²+bx+c), then select three cells in a row and enter the formula =linest(known-y's, known-x's^{1,2},1,) and hit control shift enter. This puts the coefficients a,b&c in the three selected cells.

This method can be modified for the degree of the polynomial by selecting the correct amount of cells and by inserting the correct sequence in the exponent of the linest function. More help available in the Excel Help file on the last two parameters of the function.

Regards.

Wickus

RE: Polynomial Trendline parameter Extraction

It's been a few years, but I used to curve fit 3d surfaces with this method.  The method can be applied to any type of equation.  I will start with a general description of the method, and if anyone is intrested they can e-mail for a detailed explanation.
Using a linear curve fit (y=Ax+B).  Make variables "A" and "B" set them to a value of 1.  Then using your y values and A anb B make an estimate of x.  Then make a column of x minus estimated x, squared "(x-xest)^2".  Make a sum of the new column, and either use the built-in solver, or circular references to adjust A and B so that the Sum of "(x-xest)^2" is minimizied.  With a little adjustment this method can be used to create curve fits to any type of equation you can think up.

Regards


Melvin Hakes

RE: Polynomial Trendline parameter Extraction

Hi,
It is true that the default equation displayed under the trendline options can be badly wrong, however this is usually due to the nuber format display which can be adjusted. If the aim is just to plug in the equation so as to interpolate between values consider the excellent interp and spline functions provided by the xlxtrfun addon (see link below, a must for any engineer using EXCEL).

www.netrax.net/~jdavita/XlXtrFun/XlXtrFun.htm  ;

Regards

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

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
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

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