LINEST does not produce the correct coefficients and does not match the coefficients calculated on chart trendline

Excel 365
With Steinhart-Hart Equation
Only three parameters. Same R^2
https://www.mediafire.com/file_premium/z9clyll24mmish4/05_04_23.xlsx/file
https://www.mediafire.com/file_premium/taj0jfkzajjrac6/05_04_23.pdf/file

Hans_627 wrote:

when I use:

=LINEST(B2:B42,A2:A42^{1,2,3,4,5},TRUE,FALSE)

The coefficients are incorrect

They are different. But who's to say which is "correct".

Okay, some statistics do suggest that the trendline coefficients are a better fit.

But the fit with the LINEST coefficients is not so much worse.

In fact, more estY based on the LINEST coefficients are closer to Y than the estY based on the trendline coefficients, being careful to copy the latter with 15 significant digits.

HansV wrote:

It's probably because the variation in the x-values is too small relative to the size of the values. If you subtract 2 or 3 from the x-values, the coefficients of the trendline and of the LINEST formula will conform to each other.

It is even easier than that. If just the first value is 3.286134-0.04, they conform.

But if the first value is 3.286134-0.03 or closer, they differ.

I believe the problem is the collinearity test in LINEST. I could provide more details. But even the following might be TMI.

The point is: LINEST sees the parameter A2:A42^{1,2,3,4,5} as a multiple linear regression problem.

And x^4 does seem to be collinear with a*x^5 + b*x^3 + c*x^2 + d*x + e, for some a, b, c, d and e. (We can use LINEST to derive those coefficients.)

We don't know how trendline sees the problem.

Since trendline knows that it is doing a polynomial regression, it might use a very different algorithm.

Alternatively, at the very least, trendline might not do a collinearity test and arbitrarily exclude x^4 terms. Instead, it might just let the chips fall where they may.

The collinearity RSQ difference is so small when we use 3.286134-0.04 vs 3.286134-0.03 that I suspect LINEST would work just fine (i.e. agree with the trendline) if it simply ignored the collinearity test.

(FYI, the edit history is to work around this forum's mangling of the posting. Sigh.)

It's probably because the variation in the x-values is too small relative to the size of the values. If you subtract 2 or 3 from the x-values, the coefficients of the trendline and of the LINEST formula will conform to each other.