Bug 70798 - Add support for new and renamed Excel 2010 functions
Summary: Add support for new and renamed Excel 2010 functions
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: Other All
: medium normal
Assignee: Winfried Donkers
URL:
Whiteboard: target:4.3.0 target:4.2.0.0.beta2
Keywords:
Depends on: 44134 61002 70000 70797 71008 71081 71350 71436 71720 71722 71936 72158 72197 72793 73146 73147 73148 73149
Blocks:
  Show dependency treegraph
 
Reported: 2013-10-23 12:08 UTC by Winfried Donkers
Modified: 2015-01-22 16:41 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Excel2010-functions-part-I.xlsx (from Excel 2010) (10.96 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-10-25 13:18 UTC, Gerry
Details
Excel2010-functions-part-II.xlsx (from Excel 2010) (12.03 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-11-03 10:25 UTC, Gerry
Details
Excel2010-functions-part-III.xlsx (from Excel 2010) (13.46 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-11-08 16:01 UTC, Gerry
Details
Excel2010-functions-part-IV.xlsx (from Excel 2010) (10.91 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-11-08 16:13 UTC, Gerry
Details
xlsx document for unit tests of Excel 2010 functions (9.89 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-11-17 14:08 UTC, Winfried Donkers
Details
xlsx document for unit tests of Excel 2010 functions (9.88 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-11-19 17:32 UTC, Winfried Donkers
Details
xlsx document for unit tests of Excel 2010 functions (14.21 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-11-20 17:15 UTC, Eike Rathke
Details
xlsx document for unit tests of Excel 2010 functions (14.96 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-11-20 17:24 UTC, Eike Rathke
Details
xlsx document for unit tests of Excel 2010 functions (11.08 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-11-21 10:17 UTC, Winfried Donkers
Details
screendump of F.DIST getting hint of F.DIST.RT (74.53 KB, image/gif)
2013-11-21 16:11 UTC, Winfried Donkers
Details
xlsx document for unit tests of Excel 2010 functions (15.08 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-11-22 12:23 UTC, Eike Rathke
Details
xlsx document for unit tests of Excel 2010 functions (11.15 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-12-29 15:51 UTC, Winfried Donkers
Details
xlsx document for unit tests of Excel 2010 functions (11.68 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-04-04 11:54 UTC, Winfried Donkers
Details
testcase document as saved by Excel2013 (15.70 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-04-28 08:09 UTC, Eike Rathke
Details
dummy file to make old attachment obsolete (10.24 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-04-28 08:24 UTC, Winfried Donkers
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Winfried Donkers 2013-10-23 12:08:13 UTC
Excel 2010 introduced some new functions and renamed others. Currently, these functions are not all supported by Calc.

This bug will act as metabug for all separate function-additions.
Comment 2 Gerry 2013-10-25 13:18:07 UTC
Created attachment 88108 [details]
Excel2010-functions-part-I.xlsx (from Excel 2010)

First batch of tested functions, see attachment (I follow the order of the file http://office.microsoft.com/en-us/excel-help/what-s-new-changes-made-to-excel-functions-HA010355760.aspx):

ASINH function ->	yes, works in Calc
	
BETA.DIST function (new, option "cumulative" added compared to old function) ->	no, error
BETADIST function (old compatability function) ->	yes
	
BETA.INV function ->	no, error
BETAINV function  (old compatability function) ->	yes
	
BINOM.DIST function (new) ->	no, error
BINOMDIST function (old compatability function) ->	yes
	
BINOM.INV function (new) ->	no, error
CRITBINOM function (old compatability function) ->	yes
	
CHISQ.INV.RT function, CHIINV function (already in bug 70797)	
	
CHISQ.TEST function (new) ->	no, error
CHITEST function (compatability function) ->	yes
	
CONVERT function ->	yes
	
CUMIPMT function -> 	yes
	
CUMPRINC function -> 	yes
	
ERF function -> 	yes
	
ERFC function -> 	yes
	
F.DIST.RT function (new) ->	no, error
FDIST function (old compatability function) ->	yes
	
F.INV.RT function (new) ->	no, error
FINV function (old compatability function) ->	yes
	
FACTDOUBLE function -> 	yes

@Winfried: Was this the kind of document you were looking for? The other functions from the Microsoft list will follow in the next days (I have only limited time at the moment)
Comment 3 Winfried Donkers 2013-10-25 15:04:34 UTC
(In reply to comment #2)
> @Winfried: Was this the kind of document you were looking for? The other
> functions from the Microsoft list will follow in the next days (I have only
> limited time at the moment)

@Gerry: Well yes, this is great and very useful. 
Thank you very much for your prompt and welcome assistance!
Don't worry about limited time, my time is limited as well and for various reasons (traceability of the patches for one) each new function or function pair will have a separate patch in de codebase.
So your first document will do well for some time.
To save you some time for next attachments: the 'old' Excel functions work and need not necessarily be added. Also your comment in columns A and C is not essential. Also, the information in comment #2 is not essential as the problem is 100% clear.
I have the list of functions currently missing in LibreOffice and for testing I will be using column B.

My intention is submit the patch for bug 61002 soon, possibly this weekend, and then start on the next function on the list.
Comment 4 Gerry 2013-11-03 10:25:42 UTC
Created attachment 88550 [details]
Excel2010-functions-part-II.xlsx (from Excel 2010)

Second batch of functions new in Excel 2010.
Comment 5 Gerry 2013-11-08 16:01:55 UTC
Created attachment 88895 [details]
Excel2010-functions-part-III.xlsx (from Excel 2010)

Third batch of functions new in Excel 2010.
Comment 6 Gerry 2013-11-08 16:13:37 UTC
Created attachment 88896 [details]
Excel2010-functions-part-IV.xlsx (from Excel 2010)

The fourth and last part of functions
Comment 7 Winfried Donkers 2013-11-09 14:38:30 UTC
(In reply to comment #6)
> Created attachment 88896 [details]
> Excel2010-functions-part-IV.xlsx (from Excel 2010)
> 
> The fourth and last part of functions

@Gerry: Great! Thank you, it helps a lot with testing the code modifications.
I don't think I will have all renamed/new function ready before version 4.3, but you never know :)
Comment 8 Winfried Donkers 2013-11-09 14:40:07 UTC
(In reply to comment #7)
> @Gerry: Great! Thank you, it helps a lot with testing the code modifications.
> I don't think I will have all renamed/new function ready before version 4.3,
> but you never know :)

(For 4.3 read 4.2)
Comment 9 Winfried Donkers 2013-11-17 14:08:23 UTC
Created attachment 89360 [details]
xlsx document for unit tests of Excel 2010 functions

@Eike:
Attached document contains (AFAICS) all renamed and new Excel 2010 functions.
Functions which can be used cumulative or non-cumulative are entered in both options.
Where known and verified with the pushed patches (with thanks to Gerry!) I have entered the result value as it should be in the column next to the functions.
My intention is to use the unit tests both for checking if import from xlsx goes OK and if the calculation is correct.

Document has been produced with Calc (master + F-distribution functions).
Marked functions have not yet been verified by my, either because the patch isn't there yet or because I don't have an Excel-calculated result.
I hope that you can enter those cells (I did copy of formula and paste-special of value to get the same result) in Excel2010/2013.

Once this document has been pushed to master, I will change the unit test in 
sc/qa/unit/subsequent_filter-tests.cxx, testFunctionsExcel2010().

Meanwhile, I've still got some functions to be patched :)
Comment 10 Winfried Donkers 2013-11-19 17:32:41 UTC
Created attachment 89481 [details]
xlsx document for unit tests of Excel 2010 functions

corrected illegal arguments for HYPGEOM.DIST.
Comment 11 Eike Rathke 2013-11-20 17:15:34 UTC
Created attachment 89539 [details]
xlsx document for unit tests of Excel 2010 functions

Loaded in Excel 2013 and re-saved to be sure we read what Excel writes.
Corrected functions T.DIST.2T and T.INV.2T that were loaded (written) as _xlfn.t.dist.t2 and _xlfn.t.inv.t2 instead (note 2T vs t2).
Comment 12 Eike Rathke 2013-11-20 17:24:44 UTC
Created attachment 89540 [details]
xlsx document for unit tests of Excel 2010 functions

Now with result column and equal test.
Comment 13 Winfried Donkers 2013-11-21 10:17:03 UTC
Created attachment 89583 [details]
xlsx document for unit tests of Excel 2010 functions

-added EXPON.DIST (non-cumulative), row 23, result is yet to be verified
-removed background colour (to prevent changing this document every time a function is added to calc)
Comment 14 Winfried Donkers 2013-11-21 16:11:42 UTC
Created attachment 89597 [details]
screendump of F.DIST getting hint of F.DIST.RT

xlsx file used is attachment 89583 [details]
build is master plus code for F-distribution and Expon/Hypgeom/Poisson/Weibull distributions.
Comment 15 Eike Rathke 2013-11-22 12:23:25 UTC
Created attachment 89635 [details]
xlsx document for unit tests of Excel 2010 functions

Corrected the "Equal?" column that starting at the modified row referenced wrong cells so all following results did not pass and re-saved document in Excel.
Comment 16 Winfried Donkers 2013-11-22 12:32:08 UTC
(In reply to comment #15)
> Created attachment 89635 [details]
> xlsx document for unit tests of Excel 2010 functions
> 
> Corrected the "Equal?" column that starting at the modified row referenced
> wrong cells so all following results did not pass and re-saved document in
> Excel.

That's exactly what I did too. I must have messed up (likely) or there is something not quite right with calc to Excel and vice versa.
Comment 17 Commit Notification 2013-11-22 15:40:35 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=52999789258aa7cfde8d01ff7e8a03a0f53278db

more tests for new Excel 2010 functions, fdo#70798



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 18 Commit Notification 2013-11-22 16:37:31 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=0d94f14ba641752401f6782f75c015f3f031f679&h=libreoffice-4-2

more tests for new Excel 2010 functions, fdo#70798


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.
Comment 19 Winfried Donkers 2013-12-25 14:24:10 UTC
(In reply to comment #15)
> Created attachment 89635 [details]
> xlsx document for unit tests of Excel 2010 functions
> 
> Corrected the "Equal?" column that starting at the modified row referenced
> wrong cells so all following results did not pass and re-saved document in
> Excel.

@Eike,
row 66 (T.INV.2T) is not correct. Argument 1, the probability, is 1.3333...
(Didn't see it earlier, as I only just completed the patch for T.INV.2T. Will probably submit that patch with the test for this row switched off.)
Comment 20 Winfried Donkers 2013-12-29 15:51:14 UTC
Created attachment 91294 [details]
xlsx document for unit tests of Excel 2010 functions

corrected formula call to T.INV in row 66.
added formula call to T.DIST (non-cumulative).

@Eike:
Document made with calc, so should be tested/written with Excel 2010/2013 before pushing to master.
Could you do that, please?
I will change the lines in /sc/qa/unit/subsequent_filters-test.cxx once the xlsx document has been pushed.
Comment 21 Winfried Donkers 2014-01-30 18:30:28 UTC
@Eike: BTW, for a proper test of MODE.MULT the result should be an array with more than 1 value. The current test is neither an array-function call, nor has the result multiple values.
It would probably be best if that test were made in Excel (and my code isn't satisfactory yet, too)
Comment 22 Commit Notification 2014-03-05 12:03:13 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=9b808856ca26a1879303bd562f6b02233cb87ac3

fdo#70798 add missing parclass.cxx entries for new Excel2010 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 23 Winfried Donkers 2014-04-04 11:54:41 UTC
Created attachment 96899 [details]
xlsx document for unit tests of Excel 2010 functions

Unit test document with extra and more meaningful tests (accompanying patch with modified testing code to be submitted to gerrit soon).
Comment 24 Commit Notification 2014-04-04 13:27:17 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=50820ace559d715891e35d9cf1213001049c96a4

updated test document for new Excel functions, fdo#70798



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 25 Eike Rathke 2014-04-04 13:29:24 UTC
Excel produces different results for some NETWORKDAYS.INTL and WORKDAY.INTL calls. Loaded in Excel, adapted, saved and committed with the above commit.
Comment 26 spbkumar023 2014-04-26 22:42:32 UTC
(In reply to comment #25)
> Excel produces different results for some NETWORKDAYS.INTL and WORKDAY.INTL
> calls. Loaded in Excel, adapted, saved and committed with the above commit.

Are the functions NETWORKDAYS.INTL AND WORKDAY.INTL usable in Libre Office.
Because that may help me solve my problem posted at 

https://bugs.freedesktop.org/show_bug.cgi?id=77985
Comment 27 Winfried Donkers 2014-04-28 06:31:27 UTC
(In reply to comment #26)
> (In reply to comment #25)
> > Excel produces different results for some NETWORKDAYS.INTL and WORKDAY.INTL
> > calls. Loaded in Excel, adapted, saved and committed with the above commit.
> 
> Are the functions NETWORKDAYS.INTL AND WORKDAY.INTL usable in Libre Office.
> Because that may help me solve my problem posted at 
> 
> https://bugs.freedesktop.org/show_bug.cgi?id=77985

Yes they are, starting with version 4.3.0 (see bug 79147).
Comment 28 Eike Rathke 2014-04-28 08:09:13 UTC
Created attachment 98107 [details]
testcase document as saved by Excel2013
Comment 29 Winfried Donkers 2014-04-28 08:24:28 UTC
Created attachment 98108 [details]
dummy file to make old attachment obsolete

Obsoleted document is no longer valid; the xlsx document for unit test can be found in the codebase /sc/qa/unit/data/xlxs/functions-excel-2010.xlsx