Bug 70797 - Direct function mapping between Excel 2010+ & Calc for CHISQ.TEST, Floor.precise, Ceiling.precise, CHISQ.INV.RT, CHISQ.DIST.RT, and more
Summary: Direct function mapping between Excel 2010+ & Calc for CHISQ.TEST, Floor.prec...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.1.1.2 release
Hardware: Other All
: medium normal
Assignee: Winfried Donkers
URL:
Whiteboard: target:4.2.0
Keywords:
Depends on:
Blocks: 70798
  Show dependency treegraph
 
Reported: 2013-10-23 12:01 UTC by Gerry
Modified: 2013-12-03 18:02 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments
New-Excel2010-functions-causing-error-in-Calc.xlsx (11.28 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-10-23 12:01 UTC, Gerry
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gerry 2013-10-23 12:01:36 UTC
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
Comment 1 Winfried Donkers 2013-10-24 14:44:04 UTC
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!
Comment 2 Gerry 2013-10-25 13:21:28 UTC
Hi Winfried,

Thanks! I just added an attachment to bug 70798, first part of tested functions.
Comment 3 Commit Notification 2013-11-08 17:41:23 UTC
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.
Comment 4 Winfried Donkers 2013-11-17 14:14:55 UTC
@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!
Comment 5 Gerry 2013-11-17 14:25:29 UTC
Thanks a lot, Winfried! I close this bug here as there is the meta bug 70798
Comment 6 Winfried Donkers 2013-12-03 16:47:08 UTC
CHISQ.DIST does not have the same argument handling in LibreOffice as it has in Excel; patch is being made right now.
Comment 7 Commit Notification 2013-12-03 17:51:16 UTC
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.
Comment 8 Commit Notification 2013-12-03 18:01:53 UTC
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.