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.
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
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.
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.
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.
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.
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.
Created attachment 120219 [details] head with origin formula
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.
I'm investigating.
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
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.
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.
Pending review https://gerrit.libreoffice.org/20731 for 5-0
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.