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.
Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.