Thread: 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!

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.

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.

Hmm... this is totally blowing my mind 0_0... makes no sense what so ever. :S