Description: After many trials, the most minimal setup I could find to reproduce the bug still takes two many steps to describe here. Steps to Reproduce: Please open the (very short) attached ods file and try changing A.A1 to any value. Apparently, the bug needs MULTIPLE.OPERATIONS and a remote ref and two SUM functions one below the other in order to be reproduced. Very weird... Actual Results: A.A2 = 1000 Expected Results: A.A2 should be equal to A.A1 Reproducible: Always User Profile Reset: Yes Additional Info: I could reproduce this bug on LibreOffice Calc 5.2.3.2 on Kubuntu Yaketi and on LibreOffice Calc 5.1.6.2 on Windows 7. User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:49.0) Gecko/20100101 Firefox/49.0
Created attachment 128486 [details] Please use this file in order to reproduce the bug
This bug might be related to https://bugs.documentfoundation.org/show_bug.cgi?id=76447
I think, there is a problem, but I disagree, that "A.A2 should be equal to A.A1". I think the result in cell A.C2 should be the value of A.C1. Cell A.C2 has content "=MULTIPLE.OPERATIONS($A$2;$A$3;$B2;$A$1;C$1)" That means: The formula template is in A.A2 The first variable in the formula template is A.A3 The actual value to replace the first variable is in A.B2 The second variable in the formula template is A.A1 The actual value to replace the second variable is in A.C1 Cell A.A2 has content "=B.C1". This formula has neither a direct reference to A.A3 nor to A.A1. The reference "=B.C1" is resolved to "=SUM(B.A1:B.B1)" and that should be handled as "=B.A1+B.B1" and with resolving the reference in B.A1 it should be handled as "=A.A1+B.B1". Now we do the replacements. Instead of the variable A.A1 the replacement from A.C1 is used and we get the calculation "=A.C1+B.B1" with their current values "=1000+empty" resulting in value 1000, which can be seen in cell A.C2. But if you change A.A1 e.g. to 123 and use Ctrl+Shift+F9 to hard recalculate, you get as result the value 123. It seems that the references in the arguments of SUM are not resolved for purpose of MULTIPLE.OPERATIONS. If you use B.A1+B.B1 instead of SUM, then the reference chain of MULTIPLE.OPERATIONS resolves to "=A.A1+B.B1" and the replacement A.C1 instead of A.A1 works. This issue needs a Calc-expert to explain, what result is the correct one for MULTIPLE.OPERATIONS.
(In reply to Regina Henschel from comment #3) > I think, there is a problem, but I disagree, that "A.A2 should be equal to > A.A1". Well, A.A2 has the formula "=$B.C1" and B.C1 has the formula "=$A.A1", so it's pretty clear that A.A2 = A.A1 (with just B.C1 between them). I agree with what I understood from the rest of your comment. Ctrl+Shift+F9 make the calculation right (with A.A2 = A.A1) in the case of this file, which is the most minimal example of the bug I could find. But in my real workbook with 7 interconnected sheets and much more calculations, the problem is compounded and many values are not right (some in the MULTIPLE.OPERATIONS cells, some elsewhere), and a hard recalculation doe not help in this case. I could attach the file here but I doubt you could navigate easily among the dependencies between cells. Suffice to say a MULTIPLE.OPERATIONS formula should not interfere with the calculation of the rest of the cells. The manual recalculation (Ctrl+Shift+F9) command is useless when there are multiple dependent sheets that need a recalculation in a precise order in order to be right. The order (and possibly the concurrence) of recalculations in the different sheets might be the culprit here.
(In reply to Vianney Stroebel from comment #4) > (In reply to Regina Henschel from comment #3) > > I think, there is a problem, but I disagree, that "A.A2 should be equal to > > A.A1". > > Well, A.A2 has the formula "=$B.C1" and B.C1 has the formula "=$A.A1", so > it's pretty clear that A.A2 = A.A1 (with just B.C1 between them). I agree that the formula template in A.A2 resolves to being actually "=A.A1". But you have not considered, that "=MULTIPLE.OPERATIONS($A$2;$A$3;$B2;$A$1;C$1)" has the replacement rule to use the value of A.C1 at all places where the formula template has the reference to A.A1. For definition of MULTIPLE.OPERATIONS read http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018432_715980110
> I agree that the formula template in A.A2 resolves to being actually > "=A.A1". But you have not considered, that > "=MULTIPLE.OPERATIONS($A$2;$A$3;$B2;$A$1;C$1)" has the replacement rule to > use the value of A.C1 at all places where the formula template has the > reference to A.A1. Don't you agree that MULTIPLE.OPERATIONS should only affect the value of the cells that actually contains the "MULTIPLE.OPERATIONS" function? If so, why should the presence of a MULTIPLE.OPERATIONS function in A.C2 affect the value of A.A2?
(In reply to Vianney Stroebel from comment #6) > > I agree that the formula template in A.A2 resolves to being actually > > "=A.A1". But you have not considered, that > > "=MULTIPLE.OPERATIONS($A$2;$A$3;$B2;$A$1;C$1)" has the replacement rule to > > use the value of A.C1 at all places where the formula template has the > > reference to A.A1. > > Don't you agree that MULTIPLE.OPERATIONS should only affect the value of the > cells that actually contains the "MULTIPLE.OPERATIONS" function? > > If so, why should the presence of a MULTIPLE.OPERATIONS function in A.C2 > affect the value of A.A2? You are right that using MULTIPLE.OPERATIONS in A.C2 should have no effect on the formula result in A.A2. But my concerns are these and therefore I set it to New: A.C2 should show the value of A.C1, but that is not the case after you force recalculate after a change in A.A1. B.A1 should show the value of A.A1. But that is not the case after you change the value in A.C1. My guess is, that the problem is the range in the SUM function in B.C1. Without this range it works as expected.
(In reply to Regina Henschel) I think I understand each of your messages but I don't understand your point. If you're just confirming the bug that I filed, then... ok... we have no disagreement then.
I just confirm it and list some additional problems.
The problem seems to be a shared grouped formula that is created from the two subsequent SUM formulas in B.C1:C2, changing one of them, for example in C1 to =A1+B1 or in C2 to =A2+B2, makes the calculation work as expected.
(In reply to Eike Rathke from comment #10) > The problem seems to be a shared grouped formula that is created from the > two subsequent SUM formulas in B.C1:C2, changing one of them, for example in > C1 to =A1+B1 or in C2 to =A2+B2, makes the calculation work as expected. Is a "shared grouped formula" a concept of LibreOffice Calc?
A concept of Calc, a concept of Excel, maybe others as well.
(In reply to Eike Rathke from comment #12) > A concept of Calc, a concept of Excel, maybe others as well. What exact name does this concept have in Calc? Googling 'libreoffice calc "grouped formula"' or 'libreoffice calc "group formula"' doesn't produce anything.
It's an implementation detail, you'd need to dive into the formula cell related source code under sc/source/core/{data,tool}
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=05d2a66955f8a6552a79696474386ca9f45f9ef2 Resolves: tdf#103701 bulk-broadcast the correct hint ID for formula groups It will be available in 5.3.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review https://gerrit.libreoffice.org/30701 for 5-2
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=e64460d8d2e6deeb234d744f7618f11e70385e81&h=libreoffice-5-2 Resolves: tdf#103701 bulk-broadcast the correct hint ID for formula groups It will be available in 5.2.4. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.