Created attachment 112452 [details] test documents before and after the export to xlsx In bug 59819 functions IFNA and IFERROR was added. According to specification is second argument of functions mandatory. spec> http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018448_715980110 Calc allows create functions without second argument and returns results. After export to XLSX this create incompatibility. Also export of function IFNA to XLSX is broken (same symptoms as in bug 59819). Steps to reproduce Error1: - open iferror_ifna_second argument.ods - save as .xlsx - open in excel Actual results Error message: broken file, repair. After repair: A1 = 1 (cached value; formula lost) A2= =_xlfn.IFNA(F1) *** IFERROR with second argument is correctly exported to .xlsx. IFNA with second argument is not correctly exported to .xlsx -> =_xlfn.IFNA(E1;E2) Tested with LO 4.3.5, win7
According to MS help (https://support.office.com/en-nz/article/IFNA-function-6626c961-a569-42fc-a49d-79b4951fd461?ui=en-US&rs=en-NZ&ad=NZ) is function IFNA for for Excel 2013 - i tested with Excel 2010 => saving IFNA is probably correct.
I will investigate.
Calc's IFERROR and IFNA function use the same code. The code does not check the number of arguments. Neither does the code use an empty string as second argument when not given (as is the case with the Excel functions IFERROR and IFNA). I set the status to new. I suggest we use the Excel behaviour in Calc too, and put this as an OpenFormula extension in the wiki (https://wiki.documentfoundation.org/Development/ODF_Implementer_Notes/List_of_LibreOffice_OpenFormula_Extensions). Eike, Raal, do you agree?
(In reply to Winfried Donkers from comment #3) Hello, > I suggest we use the Excel behaviour in Calc too, and put this as an > OpenFormula extension in the wiki > (https://wiki.documentfoundation.org/Development/ODF_Implementer_Notes/ > List_of_LibreOffice_OpenFormula_Extensions). > > Eike, Raal, do you agree? Tested iferror with excel2010 and excel doesn't allow to create formula with only one argument, so we should do the same
(In reply to raal from comment #4) > Tested iferror with excel2010 and excel doesn't allow to create formula with > only one argument, so we should do the same OK, that's clear. I will start working on a fix.
Created attachment 112732 [details] use cases jump functions @Raal: Could you please copy the results from Excel into row B, so that I can use that to compare the behaviour? As you can see, IFERROR( a ) is no longer accepted, but empty arguments still are (until they are to be returned). IFNA is not in the document as the code is shared with IFERROR.
As the problems applies to the second and third argument of function IF() as well, I changed subject of this bug report. Fix will cover all three functions.
Winfried Donkers committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=0b75eda1090f92adc678ceff2565da2dc7d9328c tdf#88576 fix handling of empty arguments in IF(), IFERROR() and IFNA() It will be available in 4.5.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 "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=6583f4e30015164af4972921b5bb7880dfb65f65 tdf#88576 check that two parameters are given for IFERROR() and IFNA() It will be available in 4.5.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 reviews https://gerrit.libreoffice.org/14421 for 4-4 https://gerrit.libreoffice.org/14422 for 4-4-1 https://gerrit.libreoffice.org/14423 for 4-3
Winfried Donkers committed a patch related to this issue. It has been pushed to "libreoffice-4-3": http://cgit.freedesktop.org/libreoffice/core/commit/?id=14b76ff3a98898fcccfa6e48ca8ae1e7bea802e2&h=libreoffice-4-3 tdf#88576 fix handling of empty arguments in IF(), IFERROR() and IFNA() It will be available in 4.3.7. 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.
Winfried Donkers committed a patch related to this issue. It has been pushed to "libreoffice-4-4": http://cgit.freedesktop.org/libreoffice/core/commit/?id=3f7558360c4805076a1705c204c9e8dba12b7358&h=libreoffice-4-4 tdf#88576 fix handling of empty arguments in IF(), IFERROR() and IFNA() It will be available in 4.4.2. 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.