Created attachment 54800 [details] ods format - All works fine If you open a new LibreOffice Calc file, fill E2 cell with a number and Use =ISEVEN(E2) formula, it gives the adequate response (TRUE if even number, FALSE otherwise). (1)If you save the file in .ods format, everything works as expected. (2) The problem starts if you save and close the document and reopen again. In this case, it gives a #MACRO? error. If you Edit cell (E2) and press INTRO, same error. If you Edit cell (E2), move cursor (Left or right arrow) and press INTRO, OK. If you open the document in Spanish version of MS Excel 2007 or 2010, works fine. Converts formula to =ES.PAR(E2). (3)If you save the document in the Spanish version of MS Excel 2010 in .xls format and open it again in LibreOffice, it gives #ADDIN? error. Formula has changed to =ES.PAR(E2). (4)If you save the document in the Spanish version of MS Excel 2010 in .xlsx format and open it again in LibreOffice, it works OK. Formula has changed to =ISEVEN_ADD(E2). Attaching 4 files: (1)LibreofficeTest.ods - All works fine (2)LibreofficeTestLinux.xls - saved in LO as .xls - #MACRO? error (3)LibreofficeTest.xls - saved in MS2010 as .xls - #ADDIN? error (4)LibreofficeTest.xlsx - saved in MS2010 as .xlsx - OK
Created attachment 54801 [details] file saved in LO as .xls - #MACRO? error
Created attachment 54802 [details] saved in MS2010 as .xls - #ADDIN? error
Created attachment 54803 [details] saved in MS2010 as .xlsx - OK
For me the way LOdev3.5 beta2 => Excel 2010 works for file type xls and xlsx as well if you use ISEVEN The way from Excel 2010 to LOdev3.5 beta uses for both file types ISEVEN_ADD in import. That is not good for a roundtrip with xlsx, because ISEVEN_ADD is not converted to ISEVEN on export. For xls roundtrip is works, but the exported function, which belongs to ISEVEN_ADD gives a converting warning in Excel. Excel 2010 can handle ISEVEN for file type xls and for file type xlsx. Perhaps it is time to drop ISEVEN_ADD at least for file type xlsx? I use LOdev3.5 beta2 on WinXP and German Excel 2010. Gorka Navarrete: It is not clear to me, whether you use new documents when you save in MS2010 or save in LO, or whether you try a roundtrip. Please specify exactly the way creation => save => load => resave => reload
Regina, I did a roundtrip. I created the documents in LO. In the process I describe in the bug report there is no more file creation that the original in LO. The rest comes from "Save as".
reproduced in 3.3.4 and 3.5.3 on Fedora 64 bit and 3.5.2 on Windows XP 32 bit ods from first attachment saved in xls using LO and then loaded by it again. As result, cells in first column contain error "Macro?" changing version to 3.3.4 as most early reproducible
I can confirm Regina's description: ISEVEN() is replaced by ISEVEN_ADD(). But there are no more macro errors with Libo 4.2.5.
** 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 (4.4.1 or later): 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 your help! -- The LibreOffice QA Team This NEW Message was generated on: 2015-07-18
** 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
Rechecking this: * Saving the example attachment 54800 [details] as XLSX in current Calc 7.1alpha the exported function is ISEVEN, works the same on reload as the ODS. * Saving the example attachment 54800 [details] as XLS in current Calc 7.1alpha the exported function is ISEVEN but on reload it's opened as ISEVEN_ADD, works the same as the ODS. Opening these in Excel gives different result in case of the fifth line: In Excel 13 the IF function gives a different result on IF(ISEVEN(E3)=1;1;0) and =IF(ISEVEN(E2)=1;1;0) probably only accidentally gives the same result as in Calc: IF can evaluate comparison with numbers in Calc as true/false while they seem always to be false inc Excel. ISEVEN(E3)=1 as condition of IF is really ISEVEN(2)=1 which makes the condition TRUE=1 I'm not sure if this is a bug or a feature, in any case it's not clearly documented at: https://help.libreoffice.org/7.1/en-US/text/scalc/01/04060105.html?DbPAR=CALC#bm_id3150141 When the ODS is saved in Excel 13 as XLSX and opened in Calc, it opens as ISEVEN and works the same as the ODS - but Excel cached the result of IF(ISEVEN(E3)=1;1;0) so a hard recalc is needed (unlike in case of LO-saved XLSX). All in all this can be considered good on our end. When the ODS is saved in a Hungarian Excel 13 as XLS and opened in Calc, it opens as =IF(PÁROSE(E3)=1;1;0) with English locale Calc and as =HA(PÁROSE(E3)=1;1;0) with Hungarian locale Calc in the input line but the grid in both cases displays the #ADDIN? error. This part is annoying, but should be an extremely rare case.
Created attachment 164712 [details] The example file saved & reopened with Calc as XLS/XLSX
Created attachment 164713 [details] The example file saved with Calc as XLS/XLSX & opened in Excel
Created attachment 164714 [details] The =IF(ISEVEN(E3)=1;1;0) in the function wizard of Calc / Excel
Created attachment 164715 [details] The example file saved with Excel as XLS/XLSX and opened in Calc
Created attachment 164716 [details] The example file saved with Excel as XLS This might be the most problematic in some extra rare cases.
Created attachment 164717 [details] The example file saved with Excel as XLSX Just for reference
@Eike could you advise on the IF situation? Is this seemingly different evaluation a bug or an underdocumented feature?
This is nothing about the IF() function. It is ISEVEN() (and others, like ISODD, GCD, LCM, ...) that in older Excel versions exist only as Add-In functions, hence are stored differently in .xls BIFF and apparently when Excel writes that in an (older?) localized version it confuses the hell out of that and writes the localized name (in attachment 54802 [details] as ES.PAR in LBL - Internal Defined Name, or in attachment 164716 [details] as PÁROSE in EXTERNNAME - Externally Defined Name record, seems different Excel versions even do it differently) which of course Calc doesn't know. Nothing we can do about other than adding a pile of translations for the old Add-In functions just to satisfy a legacy document format. Conclusion: do not use .xls BIFF to store roundtrip documents. Additionally, older Excel versions don't do an automatic conversion from boolean to number (or vice versa), so an expression like ISEVEN(E2)=1 is *always* FALSE because ISEVEN() returns boolean and boolean=number is never equal. Modern Excel versions handle that. Don't ask me for numbers when that started. For older versions you'll need to write ISEVEN(E2)=TRUE() or N(ISEVEN(E2))=1 but in this case a simple ISEVEN(E2) would had been sufficient anyway. The only case left then is (2)LibreofficeTestLinux.xls - saved in LO as .xls - #MACRO? error but that doesn't happen anymore (tried in LO7 and LO5.3), loading the .ods and saving as .xls and reloading calculates fine, even with the Add-In function ISEVEN_ADD() (only the automatic BOOLEAN format on A2 and A3 is lost if no number format was assigned). ISEVEN_ADD() also isn't stored to .xlsx OOXML, stored is ISEVEN() instead, so we are good there.