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 trend line,polynomial trendline excel,excel trendline,polynomial approximation excel,excel polynomial trendline equation,trend line excel,trendline excel,excel polynomial trendline,,excel polynomial,polynomial excel,excel approximation,polynomial approximation in excel,polynomial trendline in excel,polynoom excel,excel polynomial approximation,excel polynomial line,excel polynomial trend,excel trend line,excel trendline polynomial,excel vba trendline,miscellaneous,polynomial trendlines,polynomials in excel,trendline coefficient polynomial excel,vba excel trendline polynomial,calculate a best trend line using the r square criteria in polynomial formula,calculating polynomial in excel,excel 2007 polynomial trendline,excel calculate polynomial coefficients,excel macro polynom,excel macro polynomial,excel macro polynomial function,excel macro trendline,excel move trendline polynomial quadratic,excel polinom increase order,excel polynom,excel polynomial add in,excel polynomial coefficients,excel polynomial trend line,excel polynomial trend line formula,excel polynomial trendline coefficients,excel polynomial trendlines,excel quadratic trend line,excel trendline function,excel trendlines,mss,polynom in excell,polynomial e in excel



Related Excel Tips

Tags: , ,

Comments

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

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