[Bug 160739] Raise a matrix to an exponential with Calc

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=160739

--- Comment #17 from ady  ---
(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 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 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 from comment 3 and I still
see Err:523.

Are there additional steps to follow?

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 160739] Raise a matrix to an exponential with Calc

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=160739

--- Comment #16 from xhertan  ---
(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.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 160739] Raise a matrix to an exponential with Calc

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=160739

--- Comment #15 from xhertan  ---
(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.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 160739] Raise a matrix to an exponential with Calc

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=160739

--- Comment #14 from Regina Henschel  ---
Created attachment 193972
  --> https://bugs.documentfoundation.org/attachment.cgi?id=193972=edit
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.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 160739] Raise a matrix to an exponential with Calc

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=160739

--- Comment #13 from ady  ---
(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.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 160739] Raise a matrix to an exponential with Calc

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=160739

Regina Henschel  changed:

   What|Removed |Added

   Severity|normal  |enhancement
 Status|UNCONFIRMED |NEW
 Ever confirmed|0   |1

--- Comment #12 from Regina Henschel  ---
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.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 160739] Raise a matrix to an exponential with Calc

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=160739

--- Comment #11 from ady  ---
(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  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.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 160739] Raise a matrix to an exponential with Calc

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=160739

--- Comment #10 from xhertan  ---
(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  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?

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 160739] Raise a matrix to an exponential with Calc

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=160739

ady  changed:

   What|Removed |Added

 CC||rafael.palma.l...@gmail.com

--- Comment #9 from ady  ---
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  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.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 160739] Raise a matrix to an exponential with Calc

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=160739

--- Comment #8 from xhertan  ---
(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.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 160739] Raise a matrix to an exponential with Calc

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=160739

Regina Henschel  changed:

   What|Removed |Added

Version|7.6.6.3 release |Inherited From OOo
 CC||rb.hensc...@t-online.de

--- Comment #7 from Regina Henschel  ---
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.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 160739] Raise a matrix to an exponential with Calc

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=160739

--- Comment #6 from xhertan  ---
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 are receiving this mail because:
You are the assignee for the bug.

[Bug 160739] Raise a matrix to an exponential with Calc

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=160739

--- Comment #5 from xhertan  ---
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?

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 160739] Raise a matrix to an exponential with Calc

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=160739

--- Comment #4 from QA Administrators  ---
[Automated Action] NeedInfo-To-Unconfirmed

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 160739] Raise a matrix to an exponential with Calc

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=160739

QA Administrators  changed:

   What|Removed |Added

 Status|NEEDINFO|UNCONFIRMED
 Ever confirmed|1   |0

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 160739] Raise a matrix to an exponential with Calc

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=160739

--- Comment #3 from xhertan  ---
Created attachment 193763
  --> https://bugs.documentfoundation.org/attachment.cgi?id=193763=edit
this is the excel file that runs good on WPS and MS excel but ails on LO Calc

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 160739] Raise a matrix to an exponential with Calc

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=160739

--- Comment #2 from xhertan  ---
Created attachment 193762
  --> https://bugs.documentfoundation.org/attachment.cgi?id=193762=edit
file that works in MS Excel and fails in LO Calc

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 160739] Raise a matrix to an exponential with Calc

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=160739

ady  changed:

   What|Removed |Added

 Status|UNCONFIRMED |NEEDINFO
 Ever confirmed|0   |1

--- Comment #1 from ady  ---
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.

-- 
You are receiving this mail because:
You are the assignee for the bug.