Linest different from trendline

**Fishhooky** · 01-22-2009, 08:41 PM

Hi All,

I am using Excel 2007

I have two sets of data.
y-series is 58.61, 58.66, 58.71 and 58.76.
x-series is 0.8313, 0.8309, 0.8305 and 0.8301.

I am trying to fit a quadratic function to this data. When I plot a curve and fit a trendline I get the coefficients for the equation ax^2+bx+c
a=1.3939e-11
b=-125
c=162.5225
The plotted line seems to fit the data fairly well.

However, I want to use the linest function and I am using the formula:
=INDEX(LINEST(B2:B5,A2:A5^{1,2}),1,1)
I change the index column number as appropriate to get the coefficients:
a=-75.23775
b=0
c=110.603763

The coefficients from linest seem to be way out. Any ideas as to how I can get the output from linest to be the same as the trendline?

**Fishhooky** · 01-22-2009, 10:56 PM

I am posting an example spreadsheet to make things easier for potential helpers.

**Andy Pope** · 01-23-2009, 07:54 AM

Have a read of Tushar Mehta's paper which mentions the fact that LINEST and chart Trendline are calculated differently.
http://www.tushar-mehta.com/publish_...nalysis/16.htm

One way would be to increase your data set by a factor of 10 and decrease the returned values by the same factor. Appears to work for your data but I have no idea how sound a math solution that is

**Fishhooky** · 01-26-2009, 07:58 PM

Hi Andy,

Thanks for your reply. I appreciate that linest and trendline calculate values differently, but one does not expect the differences that I observed. I think the coefficients given by linest are clearly wrong in this case.

I also came up with a similar solution to you. I ended up multiplying the x-series by 100 then dividing the coefficient 'a' by 10000 (100^2), 'b' by 100 (100^1) and 'c' by 1 (100^0). This is at least mathematically sound, but I'm still not sure why linest gives such strange values in this case. Seemingly if the x and y series differ by a certain factor of 10 (10^x) then it can't calculate correct coefficients for a polynomial.

Many thanks again.

**Andy Pope** · 01-27-2009, 11:14 AM

The different result would be the result of a bug.
The article mentions the routines appear to have been written by different programmers, one of whom appeared to have not coded correctly for certain data conditions.

Glad you managed to work around the problem

**kikorlz** · 10-15-2012, 04:30 PM

Sry for bumping the post, but I had some problem with the things in this post....

When i put all the values in the formula, it comes a diferent yield...

(i'm trying to interpolate some non linear yields)

NonLinear.xlsx

**arlu1201** · 10-16-2012, 06:26 AM

kikortz,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.