MINVERSE works sometimes on Excel 365, but produces only 1 output on most

[vicvanlint]

On my Excel 365 on Windows 10, MINVERSE works correctly on a 4x4 matrix for a cubic regression, but produces only one output entry for a 3x3 matrix for a quadratic regression to the same data set. 

[#2]

@vicvanlint 

Is that https://techcommunity.microsoft.com/t5/excel/problem-with-using-minverse-function/m-p/2443315/highli... the same question? 

[#3]

Yes, but it has a copy of the matrices attached.

[#4]

@vicvanlint 

Based on screenshot it's hard to check which formulas and how do you use, better to have sample file. Variants could be

[#5]

Something very strange is happening, unless my age has turned me senile. I worked extensively with Excel 20 years ago, when it didn't insert things I didn't type. I've just used the matrix you use. Without the braces I get a single entry of 6.49852. With braces I get just the formula, i.e., it doesn't recognize the = sign. When I first used MINVERSE successfully it inserted the braces for me, outside the = sign, I think. I could send the entire spread sheet, but now I don't know where to attach it.

[#6]

@vicvanlint , yes, better to play with spreadsheet. Replying on post you shall see at the bottom

browse the file to attach it.

[#7]

@Sergei Baklan 

OK I found it under Open Full Text Editor.  I'm still learning.

[#8]

@vicvanlint 

Thank you for the file.

Again, we speak about Excel 365 which supports dynamic arrays.

Your formula (in grey) was entered with Ctrl+Shift+Enter, thus returns only first element of the array. If enter as regular formula as sum of two MINIVERSE matrixes, or step by step, formula returns the matrix. #N/A appears since you use sum of matrixes with different dimensions.

Please check in attached file.

[#9]

I'm sorry, but the spread sheet had a lot of irrelevant stuff from my attempts to diagnose the problem and unintentional Excel inserts, which distracted you from the relevant section. The relevant part is the sections labeled Cubic Fit and Quadratic Fit which show the square matrix and vector with the MINVERSE command below. Please ignore anything to do with matrix addition. AE25 is the location of the output vector that was there when MINVERSE worked previously, and AD35 is the one-cell output of a MINVERSE command without braces.

[#10]

@vicvanlint 

Sorry, in your file AD35 is empty, in Z25 is MINIVERSE entered as array formula and in AE25 is MMULT, also entered with CSE

If re-enter formulas without CSE the result is

[#11]

Please ignore this one and look at the cleaned up spread sheet I sent later. It eliminates all the sad history.

[#12]

@vicvanlint 

Sorry, but I see only one file from you (which we are discussing)

[#13]

@Sergei Baklan  I'll try again

[#14]

I attached the file to my response, but I don't remember having a chance to click POST afterwards. This appears to be a reply to my reply, but I'll POST it.

[#15]

@vicvanlint 

Thank you for the file. In the formula there is implicit intersection operator "@" which returns first element of the array

If use formula without it result will be expected matrix.

Operator  could appear if open in older Excel the file created in Excel which supports dynamic arrays.

[#16]

I apologize for my incompetence. I don't know how that got there; it's one of the things that the modern EXCEL is doing to me: inserting stuff I don't intend. Is there any way I can turn off the automatic insertions? I'm perfectly willing to type in the commands, as I used to do until I stopped working about ten years ago. My only excuse is that I'm 93 years old.

[#17]

@vicvanlint 

My respect.

No, inserting of implicit intersection operator is not configurable. Maybe something is in registry settings, but I don't know about such.

On the other hand this operator appears if you open created in old Excel file in new one to ensure compatibility. Old Excel performs implicit intersection silently in background, to have same result in new Excel you shall use this operator.

More about that is here Implicit intersection operator: @ - Office Support (microsoft.com)