Excel Polynomial Trendline

October 8, 2009 by vba excel
Filed under: Excel Miscellaneous 
Sponsor

Excel Polynomial Trendline [Q]

I found the excel polynomial trendline function in Charts very useful. My question is: Are there equivalent functions? What I mean, I know about “Trend” and “Linset” functions, yet in Trendline there are Polynomial Approximations, Exponential Approximations etc… Which I couldn’t find anywhere. Are they available? How can I create the dataset according to Excel approximation?

Are there anymore Approxiamtions / Interpolations in Excel (Splines, Least
Squares, etc..)?

I’m using Office 2007.

Thanks.

Excel Polynomial Trendline [A]

All of the chart trend lines can be fit via LINES using either array formulas or transformation to linearity. A couple of comments are in order, though. For the transformed fit to be optimal, you must assume equal variance (across x values) on the transformed scale, not the original scale. Otherwise either a weighted or nonlinear fit is more appropriate; neither of which is natively available in Excel, though you could “roll you own” if you know what you are doing.

Assuming north american regional settings, the polynomial fits shown at the link assume that the x and y values are given in columns. If they are given in rows, then the power separator would be a semicolon instead of a comma.

  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Google
  • E-mail this story to a friend!
  • Live
  • MisterWong
  • Propeller
  • StumbleUpon
  • Technorati
  • TwitThis
  • YahooMyWeb
  • Print this article!

Rate This Tips:

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 1.00 out of 5)
Loading ... Loading ...


Incoming excel search terms

polynomial trendline,polynomial trendline excel,excel polynomial trendline,polynomial trend line,excel trendline,excel polynomial,mss,trendline excel,excel polynomial trendline equation,polynomial approximation excel,excel trendline function,trend line excel,polynomial trendline in excel,polynomial excel,polynomial trend,polynomial trend line excel,polynomial trend lines in excel,polynomials in excel,,excel approximation,excel trendline vba,polynomial trend excel,polynomial trend in excel,excel polynom,excel polynomial trend line,excel trend line,excel trendline polynomial,excel vba trendline,excel polynomial coefficients,excel polynomial formula,polynom excel,polynomial in excel,trendline in excel,create polynomial charts in excel,excel linest polynomial,excel polynomial approximation,excel polynomial line,excel polynomial trend,excel polynomial trend formula,excel polynominal,excel trend function polynomial,excel trendlines,polynomial approximation in excel,polynomial trend lines excel,polynomial trendline formula,polynomial trendlines,polynominal trendline,polynoom excel,quadratic trend line in excel,trend excel



Related Excel Tips

Tags: , ,

Comments

3 Comments on Excel Polynomial Trendline

  1. Don Shillady on Fri, 25th Dec 2009 9:32 pm

    2. I NEED to fit polynomials to (x,y) data pairs in columns but so far cannot find the way to do that in Excel 2007. Apparently you changed things mostly to be different so you could sell a new version. You should have only changed what needed to be changed.

  2. Don Shillady on Mon, 28th Dec 2009 3:11 pm

    3. Here is a partial solution:
    1. Prepare data in two Excel columns, X and then Y.
    2. Choose (click) “Insert” from top bar
    3. Choose (click) “Scatter, then “smooth lines with points”
    4. Choose (click) “layout”; NOT “page layout”
    5. Choose (click) “Trendline” and click “more options” at bottom of page
    6. Click “Polynomial” bubble and then increase/decrease “order”
    7. Check (click) display equation and display R-squared
    That is all I know so far, BUT (!) the equation is displayed OVER the graph (not pretty) AND when I choose order=4 for a fairly smooth curve the coefficients for the third and fourth order are given to only one-sig fig and the R-squared value is given as an integer 1. I would prefer the same number of sig figs for all the coefficients even though some are very small and I would like to see R^2 to at least four sig figs. I would like to see R^2 as 0.9999 if so instead of integer 1. For my purpose I want to see the coefficients all to the same number of sig. figs. The folks at Excel have violated the principle of backward compatibility in a big way. Anyone who can tell me how to get a graph without the equation smooshed on top of the graph, please post here. The equation and R^2 should be separated by a blank background in my opinion! Good luck with learning frustration!

  3. Don on Wed, 24th Mar 2010 12:09 am

    4. Try this.

    http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas

Have another excel answer or questions for this problem ?
Feel free to post it here..