Created attachment 88030 [details] New-Excel2010-functions-causing-error-in-Calc.xlsx Excel 2010 introduced a couple of functions, aiming to improve precision of existing functions and to replace them in the long run. Of some of these new functions, LibreOffice has equivalents and direct mapping between Excel and Calc is possible (see (http://wiki.openoffice.org/wiki/Calc/Drafts/Treatment_of_new_Excel_2010_functions), but this mapping is not yet implemented. I tested some of these functions for those direct mapping is possible. How to reproduce: Please have a look at attached Excel sheet (workbook "Functions-requiring-arrays" and workbook "Functions-requiring-two-values") and import it in Calc. Just change the source values and Calc will recalculate the formulas showing errors on all new Excel 2010 functions. I tested the import/export with: CHITEST (O.K.,old function) CHISQ.TEST (-> ERROR, although direct mapping possible) Floor (O.K.,old function) Floor.precise (-> ERROR, although direct mapping possible) Ceiling (O.K.,old function) Ceiling.precise (-> ERROR, although direct mapping possible) CIINV (O.K.,old function) CHISQ.INV.RT (-> ERROR, although direct mapping possible) CHIDIST (O.K.,old function) CHISQ.DIST.RT (-> ERROR, although direct mapping possible) There are some other new functions in Excel 2010 which I did not test. You find them listed here: http://wiki.openoffice.org/wiki/Calc/Drafts/Treatment_of_new_Excel_2010_functions
Hi Gerry, Some functions in your attachment are/have been addressed in other bug reports: COVARIANCE.P and COVARIANCE.S have been fixed as ( bug 70000 ) A fix for STDEV.P and STDEV.S is currently being reviewed ( bug 44134 ) VAR.P and VAR.S are currently in the coding phase ( bug 61002 ) The other functions in your attachment I will process next. I probably will create a separate bug for each function (set). BTW, would it be possible for you to provide an Excel 2010 or 2013 (xlsx) document with all missing functions (see also meta bug 70798 )? I don't have Excel and I would like to test all functions with an original Excel document? Your help would be greatly appreciated!
Hi Winfried, Thanks! I just added an attachment to bug 70798, first part of tested functions.
Winfried Donkers committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=75601529a807f15fa648c22e3636281981cd7623 fdo#70797 add Excel 2010 functions 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.
@Gerry, Do you mind -to keep the patches easily accessible- to close this bug as resolved and trust me to add the not-yet-patched functions under separate bug numbers, all under meta bug 70798? As you may have noticed I have used your xlsx documents to create one test document that will be used to test all new functions every time anything is changed in the code. These tests are performed automatically and meant to guard against unintentional changes/damage to the functions. Thanks for your help!
Thanks a lot, Winfried! I close this bug here as there is the meta bug 70798
CHISQ.DIST does not have the same argument handling in LibreOffice as it has in Excel; patch is being made right now.
Winfried Donkers committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=c3760e9099db9cf1be696347e2a0743a3cae1b20 fdo#70797 fix incorrect argument handling of CHISQ.DIST 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-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=a4332ca8536242d7975146bcd700ecb0acb88f31&h=libreoffice-4-2 fdo#70797 fix incorrect argument handling of CHISQ.DIST It will be available in LibreOffice 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.