Description: I am trying to raise a matrix to a high power using Calc but that's throwing the "Err:523". the same file worked perfectly fine in MS Excel and WSP. I checked the recursive formula option and it is active. I installed the version 24 and the error persists. LibreOffice Version: 7.6.6.3 OS: Linux Steps to Reproduce: 1. Fill the cells A2:C4 with a square (3x3) matrix (Mo) values 2. Fill Cell E2 with the power value. Let's say E2=7 3. Fille Cell G2 with the power counter recursive formula counter: =IF(ISNUMBER(G2),IF(G2=E2,TEXT(G2,"0"),G2+1),IF(VALUE(G2)=E2,G2,1)) 4. Select Fill the Output Matrix cells H2:J4 with the array formula: =IF(ISNUMBER(G2),IF(G2=1,A2:C4,MMULT(A2:C4,H2:J4)),H2:J4) 5. This is an array formula, need to finish with CTRL+SHIFT+ENTER Actual Results: Err:523 Expected Results: Mew Matrix M = exp(Mo,7) Reproducible: Always User Profile Reset: Yes Additional Info: I tried the same example on version LO 24 with the same error results.
In the bug report page, there is a link that says "Add an attachment". Please use it to attach a file that works for you in other spreadsheet tools. Please be aware that it will be publicly available, so, using one of those other spreadsheet tools, you might want to eliminate any private or sensitive information and save that as a copy of your original file; then attach such minimal copy.
Created attachment 193762 [details] file that works in MS Excel and fails in LO Calc
Created attachment 193763 [details] this is the excel file that runs good on WPS and MS excel but ails on LO Calc
[Automated Action] NeedInfo-To-Unconfirmed
I have attached two files that should work with LO Calc but failed. I checked these files examples on MS Excel and WPS and both worked. What additional information is needed from me to confirm this bug?
Today I updated to version 24.2 and this error persists. Version: 24.2.2.1 (X86_64) Build ID: 420(Build:1) I noticed that this report remains UNCONFIRMED. Any progress on this?
You cannot do iteration without convergence in Calc. The problem is, that in case of no convergence you get the Err 523, which overwrites the results you want to see. Bug 136897 might be related. For me it is a missing feature. The problem exists already in OOo for import from xls.
(In reply to Regina Henschel from comment #7) > You cannot do iteration without convergence in Calc. The problem is, that in > case of no convergence you get the Err 523, which overwrites the results you > want to see. > > Bug 136897 might be related. > > For me it is a missing feature. > > The problem exists already in OOo for import from xls. What do you mean that this calculation is without convergence? This is an iterative matrix multiplication to get the power of a matrix with a fix number. Since the convergence (mathematically definition) is not an issue. I think the problem is with the function of the matrix multiplication.
I am CC'ing Rafael Lima, just in case he happens to be somewhat interested in this kind of issues; apologies if this is not the case. * The samples provided are indeed using iteration, but without convergence. * ATM, Calc indeed lacks the possibility of solving iterations without convergence. * There might be a chance that using some Solver or Goal Seek models would workaround the lack of the aforementioned feature, in some cases. * The specific samples attached to this report seem to be using over-complicated formulas in more than one range. There are simpler, more-efficient equivalent formulas that could replace them, unless the attached sample files actually represent much more complicated cases (which we cannot deduce from those sample files). * The MMULT() function does not seem to be related to the issue in any way; it just happens to be used in one array formula, but the same could had happened with any other function in its place. * I would suggest going to <https://ask.libreoffice.org> and ask there for alternative solutions and possible improvements, with particular focus on what exactly is the goal (i.e. what you want to achieve), rather than focusing on the method/formula that is in use in the attached sample files.
(In reply to ady from comment #9) > I am CC'ing Rafael Lima, just in case he happens to be somewhat interested > in this kind of issues; apologies if this is not the case. > > * The samples provided are indeed using iteration, but without convergence. > * ATM, Calc indeed lacks the possibility of solving iterations without > convergence. > * There might be a chance that using some Solver or Goal Seek models would > workaround the lack of the aforementioned feature, in some cases. > * The specific samples attached to this report seem to be using > over-complicated formulas in more than one range. There are simpler, > more-efficient equivalent formulas that could replace them, unless the > attached sample files actually represent much more complicated cases (which > we cannot deduce from those sample files). > * The MMULT() function does not seem to be related to the issue in any way; > it just happens to be used in one array formula, but the same could had > happened with any other function in its place. > * I would suggest going to <https://ask.libreoffice.org> and ask there for > alternative solutions and possible improvements, with particular focus on > what exactly is the goal (i.e. what you want to achieve), rather than > focusing on the method/formula that is in use in the attached sample files. Thank you for your response. The example provided here is the simplest form of my calculations. I simplified the issue with the examples provided. These examples run perfectly fine in MS Excel, SoftMaker, and WPS Office. Only LO Calc is unable to compute this. If you can reproduce this case, Can we change the status of this bug to CONFIRMED?
(In reply to xhertan from comment #10) > If you can reproduce this case, Can we change the status of this bug to > CONFIRMED? Please carefully read comment 9. I would again suggest to go to <https://ask.libreoffice.org> for (better) alternative ways to obtain the same desired result of multiplying an array by one number (which doesn't even require the MMULT() function). As for the iteration that comes up with said number, maybe using some other functions/formulas could help (instead of the over-complicated iterative formula). Such potential alternative could work or not, depending on the real-life case, which we cannot guess with the current sample files.
There is a comment about the problem in the code https://opengrok.libreoffice.org/xref/core/sc/source/core/data/formulacell.cxx?r=9d29649e#1784 I set the report to New as enhancement request to introduce a iteration mode similar to Excel.
(In reply to Regina Henschel from comment #12) > I set the report to New as enhancement request to introduce a iteration mode > similar to Excel. The OP does not agree (nor understand, apparently) that the problem is a lack of support for iteration without convergence in Calc. According to the OP, the problem is about the MMULT() function; it is not: (In reply to xhertan from comment #0) > 4. Select Fill the Output Matrix cells H2:J4 with the array formula: > =IF(ISNUMBER(G2),IF(G2=1,A2:C4,MMULT(A2:C4,H2:J4)),H2:J4) > > Actual Results: > Err:523 > > Expected Results: > Mew Matrix M = exp(Mo,7) Changing such over-complicated (and contradicting) formula to: =IF(ISNUMBER(G2),IF(G2=1,A2:C4,1),H2:J4) eliminates the influence of MMULT() and the Err:523 will still occur. Since the report is about MMULT() being somehow wrong (which it isn't), and after repeatedly trying to explain this to the OP, then this report should be Not-A-Bug (NAB). For this ticket to be an enhancement request about non-converging iterations in Calc, _that_ should be the real content of the Summary and the initial Description, with an OP that knows what exactly is being requested, containing a clear sample case. Unfortunately, we don't have such situation here.
Created attachment 193972 [details] compare normal mult with MMult I think, that there is indeed a problem with matrix multiplication in recursion. The attached file has the same structure for the simple cell and for the matrix. In case of the simple cell the iteration works, in case of the matrix not.
(In reply to ady from comment #13) > (In reply to Regina Henschel from comment #12) > > > I set the report to New as enhancement request to introduce a iteration mode > > similar to Excel. > > The OP does not agree (nor understand, apparently) that the problem is a > lack of support for iteration without convergence in Calc. > > According to the OP, the problem is about the MMULT() function; it is not: > > > (In reply to xhertan from comment #0) > > 4. Select Fill the Output Matrix cells H2:J4 with the array formula: > > =IF(ISNUMBER(G2),IF(G2=1,A2:C4,MMULT(A2:C4,H2:J4)),H2:J4) > > > > > Actual Results: > > Err:523 > > > > Expected Results: > > Mew Matrix M = exp(Mo,7) > > Changing such over-complicated (and contradicting) formula to: > > =IF(ISNUMBER(G2),IF(G2=1,A2:C4,1),H2:J4) > > eliminates the influence of MMULT() and the Err:523 will still occur. > > Since the report is about MMULT() being somehow wrong (which it isn't), and > after repeatedly trying to explain this to the OP, then this report should > be Not-A-Bug (NAB). > > For this ticket to be an enhancement request about non-converging iterations > in Calc, _that_ should be the real content of the Summary and the initial > Description, with an OP that knows what exactly is being requested, > containing a clear sample case. Unfortunately, we don't have such situation > here. Ady, I was trying to port my research calculations from MS Excel to LO Cal when I noted that LO Cal cannot execute these simple calculations. Again, I tested those simple lines in other programs and found that only LO Cal has problems with that. I don't understand where is the overcomplicated or contradicting part of this line: =IF(ISNUMBER(G2),IF(G2=1,A2:C4,MMULT(A2:C4,H2:J4)),H2:J4) this line is executing a simple comparison to check if the number on "G2" is a number since: 1. if "G2" is a number then check that "G2" == 1, and proceed to paste the values on cells A2:C2. This is A^1 = A 2. if "G2" is a number and G2 !=1 then execute the matrix multiplication MMULT(A2:C4,H2:J4). This is A*A^(n-1) 3. if "G2" is not a number then leave the value H2:J4 untouched. Check for errors as non-numbers. with the help of a counter, I can execute the sequence in a bounded (n) iteration to reach A^n=A^(n-1)*A^(n-2)*A^(n-3)...A. The objective of this iteration is to raise a square matrix to a specific power (n). So, I can play with different powers. Again, these calculations are perfectly executed in MS Excel, WPS Office, and SoftMaker Office. Only, LO is unable to execute the iteration.
(In reply to ady from comment #13) > (In reply to Regina Henschel from comment #12) > > > I set the report to New as enhancement request to introduce a iteration mode > > similar to Excel. > > The OP does not agree (nor understand, apparently) that the problem is a > lack of support for iteration without convergence in Calc. > > According to the OP, the problem is about the MMULT() function; it is not: > > > (In reply to xhertan from comment #0) > > 4. Select Fill the Output Matrix cells H2:J4 with the array formula: > > =IF(ISNUMBER(G2),IF(G2=1,A2:C4,MMULT(A2:C4,H2:J4)),H2:J4) > > > > > Actual Results: > > Err:523 > > > > Expected Results: > > Mew Matrix M = exp(Mo,7) > > Changing such over-complicated (and contradicting) formula to: > > =IF(ISNUMBER(G2),IF(G2=1,A2:C4,1),H2:J4) > > eliminates the influence of MMULT() and the Err:523 will still occur. > > Since the report is about MMULT() being somehow wrong (which it isn't), and > after repeatedly trying to explain this to the OP, then this report should > be Not-A-Bug (NAB). > > For this ticket to be an enhancement request about non-converging iterations > in Calc, _that_ should be the real content of the Summary and the initial > Description, with an OP that knows what exactly is being requested, > containing a clear sample case. Unfortunately, we don't have such situation > here. Ady, Your new formula confirms the issue is with the MMULT function in a recursive formula in LO Calc. I tested the new formula you posted: "IF(ISNUMBER(G2),IF(G2=1,A2:C4,1),H2:J4)" and this worked fine on my side with the expected result of 1 for all cells. Please, see my previous comment of why the expected result is 1 for your new formula.
(In reply to xhertan from comment #16) > Your new formula confirms the issue is with the MMULT function in a > recursive formula in LO Calc. I tested the new formula you posted: > > "IF(ISNUMBER(G2),IF(G2=1,A2:C4,1),H2:J4)" > > and this worked fine on my side with the expected result of 1 for all cells. First, please only quote the parts of prior comments that are relevant to your new comment, instead quoting entire comments. If you just want to point to a prior comment with no particular text that you are replying to, please just leave the first line "In reply to... from comment..." and delete the rest of the quoted text. I took attachment 193762 [details] from comment 2 and edited the array formula, from: =IF(ISNUMBER(G2);IF(G2=1;A2:C4;MMULT(A2:C4;H2:J4));H2:J4) to: =IF(ISNUMBER(G2);IF(G2=1;A2:C4;1);H2:J4) (introduced with CSE) and I still get Err:523. After Recalculate Hard, still the same error. BTW, in attachment 193762 [details] from comment 2, the size of the last array (with Err:523) range H2:J5 (3 Columns X 4 Rows), is not the same size as the first range of data, A2:C4, which is not "really" an array. While in theory these factors should not be a problem for MMULT() (AFAIK), I have not tested these factors for this report. I performed a similar edition in attachment 193763 [details] from comment 3 and I still see Err:523. Are there additional steps to follow?