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.
See http://office.microsoft.com/en-us/excel-help/what-s-new-changes-made-to-excel-functions-HA010355760.aspx for the complete list.
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)
(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.
Created attachment 88550 [details] Excel2010-functions-part-II.xlsx (from Excel 2010) Second batch of functions new in Excel 2010.
Created attachment 88895 [details] Excel2010-functions-part-III.xlsx (from Excel 2010) Third batch of functions new in Excel 2010.
Created attachment 88896 [details] Excel2010-functions-part-IV.xlsx (from Excel 2010) The fourth and last part of functions
(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 :)
(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)
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 :)
Created attachment 89481 [details] xlsx document for unit tests of Excel 2010 functions corrected illegal arguments for HYPGEOM.DIST.
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).
Created attachment 89540 [details] xlsx document for unit tests of Excel 2010 functions Now with result column and equal test.
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)
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.
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.
(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.
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.
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.
(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.)
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.
@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)
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.
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).
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.
Excel produces different results for some NETWORKDAYS.INTL and WORKDAY.INTL calls. Loaded in Excel, adapted, saved and committed with the above commit.
(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
(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).
Created attachment 98107 [details] testcase document as saved by Excel2013
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