LINEST Function returning different Value from Trendline, Questions regarding MMULT fix

My first observation, since I don't have Excel on this computer, is that LINEST() and your MMULT(...) function return the same coefficients when opened in LO Calc. Perhaps one solution is to move away from Excel for this?

Years ago, when I first upgraded from 2002 to 2007, I encountered an issue with LINEST() incorrectly detecting collinearity and forcing regression constants to 0. A registry mod was suggested in accordance with a MSFT kb article: https://www.excelforum.com/excel-for...ion-error.html

Not sure if any of that really helps, but maybe another bump if nothing else.

Just watching this... if you DO get any feedback elsewhere, can I ask you to provide a link here?

I am no mathematician/statistician, not at all. I'm an analytical chemist, who likes playing with numbers. I've been playing with your data and other datasets. The problem seems to arise when a polynomial fit, with an order HIGHER than that NEEDED to achieve a good fit, is used in LINEST.

Using a 2nd, or even a 3rd order polynomial fit on your dataset, LINEST works perfectly.

The problem then is defining what is meant by "higher than that needed".

Fascinating...

I daresay you also saw this page:

https://newtonexcelbach.com/2011/01/...curve-fitting/

I tried the

=MMULT(MMULT(TRANSPOSE(Y),X^N),MINVERSE(MMULT(TRANSPOSE(X^N),X^N)))

formula in Lori Millar's comments at the end.... but (unless I was doing something daft) it, too went a bit odd with higher order polynomials. Your MMULT/LINEST combination, however, does seem pretty robust. I tidied it up a little, converting it to a O365 friendly format and will keep it for future use.

=LET(x,C4:C8,y,D4:D8,n,2,MMULT(LINEST(y,(x-AVERAGE(x))^SEQUENCE(,n)),IFERROR(COMBIN(SEQUENCE(n+1,,n,-1),SEQUENCE(,n+1,n,-1))*(-AVERAGE(x))^(SEQUENCE(n+1,,0,-1)-SEQUENCE(,n+1,0,-1)),0)))

where the value in red is the polynomial order.

Originally Posted by MrShorty

when I first upgraded from 2002 to 2007, I encountered an issue with LINEST() [...] forcing regression constants to 0.

When I open the Excel file (click here) attached to the 2013 thread in __manual__ calculation mode, I do indeed see zero for the first LINEST coefficient in two of three examples (I3 and I4).

But when I enable __automatic__ calculation mode in Excel 2010, the LINEST coefficients are corrected.

They equal the values returned by SLOPE to 15 significant digits. In fact, their binary values differ by only 1 in the least-significant bit; probably a binary arithmetic anomaly.

So whatever problem existed in Excel 2007 seems to have been corrected, assuming MSFT did not screw it up again in a later release.

(FYI, the LINEST coefficients are also corrected in Web Excel at onedrive.live.com.)

-----

Originally Posted by MrShorty

A registry mod was suggested in accordance with a MSFT kb article

Be that as it may, it would be nice know what the registry mod was.

The URL for the MSFT KB article (click here) no longer works. No surprise.

But I'm surprised that I cannot find the KB article in a wiki database that I discovered some time ago (click here).

Did you happen to keep a copy of the KB article?

Or do you happen to remember the (9-year-old !) registry mod?

Originally Posted by poternerer2

I had a problem when I was trying to fit some values into an equation using LINEST, it returned 0 for some coefficients. It clearly was different from the equation given by the trendline.

It doesn't surprise me that the LINEST and trendline coefficients might be very different.

The trendline understands that we are trying to fit an order-4 polynomial. It might have a special algorithm for that.

In contrast, LINEST sees only 4 "independent" variables. It uses a multiple linear regression algorithm (click here).

Originally Posted by poternerer2

I also read that this was something about collinearity and excel recognizing those coefficients as not important

I'm not surprised that LINEST might find collinearity among one or more of those "independent" variables. For your x-values in C4:C8:

RSQ(C4:C8, C4:C8^2) = 0.999999204972925
RSQ(C4:C8, C4:C8^3) = 0.999996820402988
RSQ(C4:C8, C4:C8^4) = 0.999992847079310

where 1 indicates an exactly linear relationship. Perhaps more to the point:

1-RSQ(C4:C8, C4:C8^2) = 7.95E-07
1-RSQ(C4:C8, C4:C8^3) = 3.18E-06
1-RSQ(C4:C8, C4:C8^4) = 7.15E-06

My guess: LINEST considers 1-RSQ < 1E-6 or equivalently RSQ > 0.999999 to be "collinear".

I don't see a problem with that.

PS.... That is over-simplified to provide the gist of it. We really need to look at the multicorrelation of each variable with all of the others.

-----

Originally Posted by poternerer2

I read from some forums that this was because of the precision limitation

IMHO, that would only explain relatively small differences.

-----

Originally Posted by poternerer2

I was able to discover some function somewhere that uses something like this

Please Login or Register  to view this content.

It would be nice know where "somewhere" is (URL) and what "something" is (specific data model and solution for it).

Off-hand, I cannot imagine why that formula would work, especially the use of COMBIN. But admittedly, I haven't taken even a millisecond to try to understand it.

In contrast, the formula presented by Lori Miller in comments (click here; thanks to Glenn) might be derived directly from the math formulas in the Multicollinearity wikipage (click here), cited by Lori, and in the Linear Regression wikipage (click here).

-----

Originally Posted by poternerer2

Can somebody shed some light on how this works or if that's not possible what concept is this in matrixes so I can read about it.

That's my question, as well. It has been 50 years since I worked with math like this. Unfortunately, I've forgotten it all; and I don't have the patience to relearn it.

-----
PS.... Besides the wikipage references above, the following are some other references that might be useful.
https://www.real-statistics.com/mult...n/collinearity
https://www.real-statistics.com/mult...hout-intercept

Disclaimer: I have not read these pages myself. And I do not have the blind faith in the website that others do. I often find mistakes -- some significant -- when I do a deep-dive. But he usually does a good job of explaining the concepts. (But not for this, IMHO.)