Results 1 to 4 of 4

Math Help - Excel Trendline

  1. #1
    Junior Member
    Joined
    Nov 2007
    Posts
    30

    Excel Trendline

    Not sure if this is the right place for this question but here it goes. I have some data which I've plotted in excel. I added a polynomial trendline of order 6 with an R^2 of 0.9997... however when I start inputting values into the equation I get results which are different to what is apparent in the data e.g. if I put in 16, I get double the value that I'm expecting. For some reason, the lower values 1,2,3 are very close to what they should be... however as I keep increasing the random variable x, the values just get out of whack really quickly... by 4 errors are evident... by 6 and over it's not even close. e.g. @ 16 the real value is 0.178 but the equation spits out 0.45.
    What's going on?

    I used matlab and I get the same results as excel so I know that it's not some bug in excel.

    EDIT: I've tried it with other data which is linear and everything works as expected and also tried a polynomial of order 2 and it works fine as well!
    Last edited by Atomic_Sheep; August 30th 2012 at 08:11 AM.
    Follow Math Help Forum on Facebook and Google+

  2. #2
    MHF Contributor

    Joined
    Apr 2005
    Posts
    15,717
    Thanks
    1472

    Re: Excel Trendline

    I'm not familiar with "Excel" but I suspect that "trendline" is giving you a "least squares approximation"- it does NOT necessarily go through the data points unless the data happens to lie on a 6 degree polynomial.
    Follow Math Help Forum on Facebook and Google+

  3. #3
    Junior Member
    Joined
    Nov 2007
    Posts
    30

    Re: Excel Trendline

    Well, as I pointed out, the R^2 of the trendline is pretty much 1, so the polynomial does a really good job or approximating the data (to a level that I'm happy with it)... so really, I 'm not sure why given this fact, if I put in the exact same values that were actually present in the data, I get vastly different results. Obviously the whole point of me having created the trendline was to be able to estimate values for points I don't have, however that's out of the question right now since even the points that as I said... made up the data for some reason don't give close enough results which tells me that there's a problem somewhere.
    Follow Math Help Forum on Facebook and Google+

  4. #4
    Junior Member
    Joined
    Nov 2007
    Posts
    30

    Re: Excel Trendline

    Hmm... this is totally blowing my mind 0_0... makes no sense what so ever. :S
    Follow Math Help Forum on Facebook and Google+

Similar Math Help Forum Discussions

  1. help in excel
    Posted in the Math Software Forum
    Replies: 1
    Last Post: May 26th 2011, 05:29 AM
  2. Calculating a value from a trendline.
    Posted in the Algebra Forum
    Replies: 5
    Last Post: October 24th 2010, 05:21 AM
  3. Calculating the Slope Of A Trendline
    Posted in the Algebra Forum
    Replies: 1
    Last Post: August 29th 2008, 09:34 AM
  4. excel
    Posted in the Math Software Forum
    Replies: 8
    Last Post: April 23rd 2008, 07:48 AM
  5. excel
    Posted in the Math Software Forum
    Replies: 1
    Last Post: January 21st 2008, 05:24 AM

Search Tags


/mathhelpforum @mathhelpforum