Download it now!
Bug 95440 - Strange recalculation formulas
Summary: Strange recalculation formulas
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.0.2.1 rc
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:5.2.0 target:5.1.0.1 target:5...
Keywords: regression
Depends on:
Blocks:
 
Reported: 2015-10-30 11:26 UTC by Juraj Václavík
Modified: 2016-10-25 19:11 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
see to cell K2 (with origin formula) and K3 (changed formula) (10.99 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-10-30 11:26 UTC, Juraj Václavík
Details
head with origin formula (10.75 KB, application/vnd.oasis.opendocument.spreadsheet-template)
2015-11-03 08:51 UTC, Juraj Václavík
Details
data file (2.73 KB, text/csv)
2015-11-03 09:03 UTC, Juraj Václavík
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Juraj Václavík 2015-10-30 11:26:17 UTC
Created attachment 120104 [details]
see to cell K2 (with origin formula) and K3 (changed formula)

Maybe similar to Bug 94924

I have a csv file, that I open in LO and I copy a head of the table (with formulas) from other table by clipboard. At the end I 'spread' the formulas by mouse over the all rows of data. Formulas are recalculated during spreading. It's OK.
But after some update of LO (maybe 4.4? or 5.0?) recalculation is not properly provided and I must edit the inserted formula between spreading. After some testing I found, that I can solve the problem by swapping two adding parts of formula:

Original formula:
=IF(OR(F2="A";F2="B";F2="D");$Y2;IF(F2="C";3,8;IF(OR(F2="E";F2="G");0,2;IF(OR(F2="M";F2="H";F2="K";F2="F";F2="S";F2="R";F2="Z");2;IF(F2="X";1;IF(F2="Y";5;IF( OR(F2="N";F2="O";F2="T";F2="U");10;IF(OR(F2="J";F2="P");6;IF(OR(F2="L";F2="V");8;0)))))))))+IF(J2>1;(J2-1)*0,75;0)

Changed formula:
=IF(J3>1;(J3-1)*0,75;0)+IF(OR(F3="A";F3="B";F3="D");$Y3;IF(F3="C";3,8;IF(OR(F3="E";F3="G");0,2;IF(OR(F3="M";F3="H";F3="K";F3="F";F3="S";F3="R";F3="Z");2;IF(F3="X";1;IF(F3="Y";5;IF( OR(F3="N";F3="O";F3="T";F3="U");10;IF(OR(F3="J";F3="P");6;IF(OR(F3="L";F3="V");8;0)))))))))

I was alerted, that correct use of function OR is 
=OR(F3="A";F3="B";F3="D")=1
but this does not solve this problem. It can be solved by ctrl-shift-F9.

See you to attached file.
Comment 1 Ákos 2015-10-30 14:37:29 UTC
In LibreOffice 3.6.7.2 the formula is calculated correctly, and after 4.0.0.1 is wrong, like in LO 5.0.3.2 or 5.1.0.0.alpha1
Comment 2 m.a.riosv 2015-10-30 22:28:23 UTC
Attached sample fails in K5 with 3.6 and more versions.

It's possible to make it works with 5.0.3 changing the option on how text are treated in formulas, K5 has references to J5 that has text '??'.
The option is in:
Menu/Tools/Options/LibreOffice calc/Formula/Detailed calculation settings/Custom - Conversion from text to number - Treat as zero.
Comment 3 Juraj Václavík 2015-10-31 08:54:09 UTC
Sorry, this sample table is rude extracted from complex data table. The ??? is a some uninterested rest. There are only interested cells K2 and K3 in this table.
Comment 4 m.a.riosv 2015-10-31 13:05:45 UTC
Looking into the file, K2 has the value '0'.

The difference between 3.6 and the new versions is that 3.6 always does an auto-calculate at opening while the new versions has an option to do that at opening to avoid large calculation in that moment with large spreadsheets.
Menu/Tools/Options/LibreOffice calc/Formula/Recalculation on file load, putting both with Always recalculate and works like in 3.6.

While no any precedent of K2 is changed then it is not recalculated. But changing the value in a precedent like F2 it is recalculated.

Hard recalc solves the issue because it forces a wholw recalculation of the spreadsheet.

Maybe th file was saved in a LibreOffice with Auto-calculate disable.

OR() function only return two values 0 or 1 so there is no need for to compare with 1.
Comment 5 Juraj Václavík 2015-10-31 19:08:19 UTC
Thank you for explain different between recalculation in LO 3.6 and later. Now I understand a part of behavior. Now I know, that the example file is confused. I try to describe problem (see you my initial report):

1. I import a csv file into LO.
2. I copy a head of the table by clipboard into the table. Formulas are part of that head.
3. I 'spread' formulas by mouse over all data rows

... AND IN THIS MOMENT THE BEHAVIOR IS DIFFERENT FOR BOTH FORMULAS:

- The first formula is NOT RECALCULATED during the spread over a data rows and value in cells is ZERO (false value).

- The second formula IS RECALCULATED CORECTLY during the spread and cells have directly the right value.
Comment 6 m.a.riosv 2015-11-01 00:37:01 UTC
Please can you attach both file, a minimal csv sample and the other with headers, I'm not able to reproduce with your sample even pasting the formula from here.
Comment 7 Juraj Václavík 2015-11-03 08:51:55 UTC
Created attachment 120219 [details]
head with origin formula
Comment 8 Juraj Václavík 2015-11-03 09:03:45 UTC
Created attachment 120221 [details]
data file

Oppps ... now I see, that both formula have the same behavior (I'll test it later) - but by my opinion the behavior is bad.

When I spread the formula (cell K5 and also T5 resp.) immediately after copy head by clipboard, in column K (and T) recalculation is not provided. 
When I edit a cell K5 after copy head by clipboard (for instance add a sign + at the begin of formula, or after 'edition' return formula without change) and spread formula after edition, formula is recalculate.
Comment 9 Eike Rathke 2015-12-15 12:39:39 UTC
I'm investigating.
Comment 10 Eike Rathke 2015-12-15 16:20:36 UTC
Happens when copying formulas containing literal strings between documents.
Simplest reproducer:
* open new document
* in A1 enter =B1="x" (result is FALSE since B1 is empty)
* copy A1 to clipboard
* open new document
* in B1 enter x
* on A1 paste formula cell from clipboard
  => result is FALSE instead of TRUE
* edit (F2, append and remove space, Enter)
  => result is TRUE
Comment 11 Commit Notification 2015-12-15 16:52:03 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=dad412e07f805a53ad73ce2e80d187a70c77e8de

Resolves: tdf#95440 SharedString are interned per document, re-intern

It will be available in 5.2.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.
Comment 12 Commit Notification 2015-12-15 17:29:52 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=4ede95a90dc299ab2026285ba8baa95b63454cc2&h=libreoffice-5-1

Resolves: tdf#95440 SharedString are interned per document, re-intern

It will be available in 5.1.0.1.

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.
Comment 13 Eike Rathke 2015-12-15 22:10:35 UTC
Pending review https://gerrit.libreoffice.org/20731 for 5-0
Comment 14 Commit Notification 2015-12-16 07:06:54 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=58c8f4dc6c7d6897e36c0c754162035f70e3385f&h=libreoffice-5-0

Resolves: tdf#95440 SharedString are interned per document, re-intern

It will be available in 5.0.5.

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.