While formatting data on different files, I copy-paste this formula from on file (ODS) to another (CSV I will save in ODS later) : =SI(EXACT(GAUCHE(B3;10);"complement");CNUM(GAUCHE(DROITE(B3;18);7));CNUM(GAUCHE(B3;7))) With English formula =IF(EXACT(LEFT(B3;10);"complement");VALUE(LEFT(RIGHT(B3;18);7));VALUE(LEFT(B3;7))) Then the results obtained when the SI/IF first argument is TRUE (B3 cell or other cells under contain "complement"), the results obtained are "Err:502". Everything works as if the first argument of SI/IF returns FALSE, even if it may be TRUE. Since the string still contains text after processing by the third argument of SI/IF, "Err:502" is quite normal there. I find a... by-pass ? (I can't remember the correct word in English) : adding spaces on each sides of the ";" of the SI/IF function reestablished the correct behaviour of SI/IF. Version: 4.4.4.2 Build ID: 40m0(Build:2) Locale : fr_FR.UTF-8 Debian unstable distribution Not tested on other environment
The bug happens only when I copy-paste
The bug happens only when I copy-paste the cell containing the formula from one file to another, not when copy-pasting the formula itself. Copiing the formula in the first file and pasting it on the cell in the second works fine too.
Hi Yann, Thanks for describing the issue. Can you please test what happens if you save the csv file as .ods first? I think see some pattern in your comment 2..
(In reply to Cor Nouws from comment #3) > Hi Yann, > > Thanks for describing the issue. > Can you please test what happens if you save the csv file as .ods first? > I think see some pattern in your comment 2.. Hi, Thanks for asking. First, I am not sure I will use the correct translation for the function I will talk about in this comment (everything between double quotes), sorry for the inconvenience if I don't. Behaviour isn't changed at all. If I load the csv then save it in ods format, then copy paste the formula's cell, the bug happens. And if I close the now-ods file and re-opened it, and then copy the formula's cell, it happened too. Moreover, I do some further testing. - The by-pass (I still don't remember the correct word) consist simply in modifying the formula. Simply adding/removing/changing one character anywhere in the string and then correcting the formula WITHOUT using "cancel last action" works too. But the "cancel last action" function restores the error. - The same bug happened when I do a "copy" of the whole sheet on an other document (ods) : all the IF functions are messed up. - if I save, close and then reload the ods, everything works fine ! - using "recalculate" doesn't work - disabling "automatic recalculate", and then copy-pasting the formula's cell works fine ! Seems like the bug comes from loaded information in LibreOffice, not from incorrect formula. "Automatic recalculate" function may be faulty here.
Please attach a minimal sample file, to test, function name is automatically translated to the language used.
Created attachment 116982 [details] Test file 1 (ODS)
Created attachment 116984 [details] Test file 2 (CSV)
I add test files, but sorry, I did not notice that LO has gently put a capital letter to the "C" of "complement" in the test file 2 (csv)... if needed I'll re-upload one To (try to) reproduce the bug: -open test file 1 -open test file 2 (tabulation separated csv), and correct the B2 cell, change the upper case C of complement to a lower case C -copy cell C from test file 1 (shown result of the formula may be 1234567)... -... to cell C2 of test file 2 Result I obtain : "Err :502" Expected results : "1212121"
Something strange is happening here, Copying the cell produce the commented result "Err :502". After copy edit the cell, e.g. add a space to the end of cell, or editing with the wizard, shows the correct result. Copying the content of the cell (not the cell) and paste, shows the correct result. Seems the issue is when the cell is copied, the condition in the if is false and takes the second value not the first. Has nothing to do with the Err value, changing the second value in the if prior to copy, this is showed after copy.
I do more testing, again : - I test if the bug is reproducible with other logic function. OR, AND, XOR, IFERROR and IFNA are not affected. - behavior of formula =IF(TRUE();"true";"false") when copy-pasted is CORRECT - behavior of formula =IF(EXACT(LEFT(B2;10);"complement");"true";"false") when copy-pasted is INCORRECT - behavior of formula =IF(EXACT("same";"same");"true";"false") when copy-pasted is CORRECT - behavior of formula =EXACT(LEFT(B2;10);"complement") when copy-pasted is INCORRECT - behevior of formula =EXACT(LEFT(B2;10);"complement") when copy-pasted from one sheet to another from the same file, or simply copied in the same file, is CORRECT So, bug come from EXACT(;) function, not from IF(;;) ! EXACT() returns systematically FALSE() when cell is copied or cell's sheet is copied from one file to another, but not in the same document. From there, I think it is out of my competences. I consider changing the title of the bug, but I am not really familiar with conditions needed to do so. Is it okay ? Does this need a higher importance, too ? And is it confirmed on other versions of LO ? (simple test is using =IF(EXACT("same";"same");"true";"false") formula)
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present on a currently supported version of LibreOffice (5.1.5 or 5.2.1 https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to "inherited from OOo"; 4b. If the bug was not present in 3.3 - add "regression" to keyword Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug-20160920
I have now upgraded my LibreOffice installation to 4.4.4.3, the EXACT function works correctly when tis cell is copied. I modified the title to match my problem. May I now change the bug's status to resolved ?
Please better as RESOLVED WORKSFORME.
So be it.