Excel 2016 (in its update January 2016) added six new functions. They seem to be not present in LibreOffice Calc. IFS https://support.office.com/en-us/article/IFS-36329a26-37b2-467c-972b-4a39bd951d45 MAXIFS https://support.office.com/en-us/article/MAXIFS-dfd611e6-da2c-488a-919b-9b6376b28883 MINIFS https://support.office.com/en-us/article/MINIFS-6ca1ddaa-079b-4e74-80cc-72eef32e6599 SWITCH https://support.office.com/en-us/article/SWITCH-47ab33c0-28ce-4530-8a45-d532ec4aa25e CONCAT https://support.office.com/en-us/article/CONCAT-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2 TEXTJOIN https://support.office.com/en-us/article/TEXTJOIN-357b449a-ec91-49d0-80c3-0e8fc845691c Overview here: https://support.office.com/en-us/article/What-s-New-and-Improved-in-Office-2016-for-Office-365-95c8d81d-08ba-42c1-914f-bca4603e1426?ui=en-US&rs=en-US&ad=US
I will have a go at them (one at a time). BTW, it is not clear to me if these functions are (or will be) present in Excel 2016 offline (i.e. installed and running on a standalone PC). If not, testing may be somewhat difficult for me.
CONCAT is the same as CONCATENATE, but it accepts more argument types, e.g. CONCAT( A1:B4 ) and CONCAT( A:A, B:B ). As this does not seem to violate the ODFF1.2 definition of CONCATENATE( {Text T}+ ), I intend to expand the accepted argument types of CONCATENATE before adding CONCAT as a duplicate of CONCATENATE. @Eike: can you confirm that CONCATENATE( A1:B4 ) and CONCATENATE( A:A, B:B ) would be compliant with http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#CONCATENATE ?
@Gerry, ERack, Dennis: Does any of you have access to MSO 365 Excel2016? The functions do not appear/have not yet appeared in the pc-installed Excel 2016, so I can't test my code.
@Winfried: Unfortunately, I don't have access to Office 365. Just LO Calc and MS Excel 2013 at work. I don't know whether the online MS office version on live.com already support these new functions.
I might have, will test them tomorrow. Do you need test documents?
(In reply to Dennis Roczek from comment #5) > I might have, will test them tomorrow. Do you need test documents? @Dennis: I will need to test import and export. Th eeasiest way is that I produce xlsx documents that can then be verified in Excel (export) and rewritten by Excel so that I can verify the import in Calc. Should you indeed have access to these functions, it would be a great help if I could send you these documents (probably one function(type) at a time).
Created attachment 123298 [details] CONCAT.ods yeah, I do have access. :-) but only the web interface and this is really bad in contrast to the fat client
Created attachment 123299 [details] concat.xslx
Created attachment 123318 [details] better CONCAT2 example
Created attachment 123319 [details] ifs.ods
Created attachment 123320 [details] ifs.xslx
Created attachment 123323 [details] minifs.ods
Created attachment 123324 [details] minifs.xlsx
Created attachment 123325 [details] MAXIFS.ods
Created attachment 123326 [details] MAXIFS.ods
Created attachment 123329 [details] TEXTJOIN.ods
Created attachment 123330 [details] TEXTJOIN.xlsx
sry winfried the German version of o365 / excel online version will not work with th switch / wechseln function. :-( Dunno how that works and thus no test file. I will report back if i get it working tomorrow. please say if the simple files does not work for you and if you need something differently. ;-)
(In reply to Dennis Roczek from comment #18) > sry winfried the German version of o365 / excel online version will not work > with th switch / wechseln function. :-( Dunno how that works and thus no > test file. I will report back if i get it working tomorrow. please say if > the simple files does not work for you and if you need something > differently. ;-) @Dennis: well, what to say, I'm still on the code for the first of the functions, you're way ahead of me ;-) You're uploads will help me test the import aspect; I will send you xlsx documents to test the export aspects - when I have my code ready to do that. A great thanks for now anyway :-) Don't spend too much time on the SWITCH function for me; I will probably save that for last and I will send you a document that you can test and save after a recalculation of the document (copy the results and past-special them in a reserved space so that I can 1) see if the interoperability is OK and 2) use the document for unit tests.
Please consider documenting these function for the help content.
(In reply to Olivier Hallot from comment #20) > Please consider documenting these function for the help content. Hi Olivier, Oh yes, I will. I even remember who helped me last time ;-) This time I will add the functions in parts. Currently I'm trying to finish the first 2 and the second batch of 2 is under development. I intend to open a separate bug report (like bug 97021) and add my texts to it. I'll cc you on that bug report, or shall I make you assignee?
Winfried Donkers committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=a76c5e21378e5364f1f7554a32d89072feff8b0c tdf#97831 [part] Add Excel 2016-Office 365 functions to Calc It will be available in 5.2.0. 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 "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=98ac8e769cee49852e1bfee5fc5a38ab20959eba check availability of stack and some nitpicks, tdf#97831 follow-up It will be available in 5.2.0. 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 "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=4905c8bf7834b1ca79139c62f4e8b0672e9ddc13 early bail out if nGlobalError, tdf#97831 follow-up It will be available in 5.2.0. 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 "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=47405e009500538732926739ae4b2636c0957238 tdf#97831 follow up improve error handling It will be available in 5.2.0. 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.
just for an update. I still do not get the switch function working - I just retried it again, with different examples by Microsoft. Excel always warns about too many arguments. (actually the "Nth" in the parameters are NOT working, one criteria / result is working) Dunno what has to be done to get that correctly working. :-( Maybe somebody other can help here...
(In reply to Dennis Roczek from comment #26) > just for an update. I still do not get the switch function working - I just > retried it again, with different examples by Microsoft. Excel always warns > about too many arguments. (actually the "Nth" in the parameters are NOT > working, one criteria / result is working) > > Dunno what has to be done to get that correctly working. :-( @Dennis: I don't understand your update. The SWITCH function is not yet implemented in Calc, but in the reviewing process (together with IFS). So far, only CONCAT and SWITCH have been implemented (starting with version 5.2). MINIFS and MAXIFS are well advanced in the development stage. Do you mean you can't get SWITCH to work in Excel directly? That would be a Microsoft bug. The SWITCH that is being added to Calc is based on the link in the description of this bug report. =SWITCH(3,1,"Sunday",2,"Monday",3,"Tuesday",4,"Wednesday","other") is to return "Tuesday" and =SWITCH(5,1,"Sunday",2,"Monday",3,"Tuesday",4,"Wednesday","other") is to return "other". That is the case with the SWITCH in review, both in ods and in xlsx documents and both in Calc and in MS Excel 2016-Office 365 (I don't have access to Excel2016-Office365 myself, but someone kindly recalculates my test documents and returns the results). Could you please clarify your comment?
Is SWITCH fonction accept ranges arguments (in Excel, in Calc)? Example : =SWITCH(C1,A1:A7,B1:B7) with From A1 to A7 : 1 to 7 From B1 to B7 : Sunday to Saturday
Hi Winfried, exactly, I do not get the stuff working in Excel Online. I do know that you (or anybody else) had not the time to implement it in Calc. (Although I'm slowly answering emails from time to time, I do read everything!) All other new functions work fine in Excel Online.
@ Winfried, I find a bug (or may be limitation) with TEXTJOIN, should I open a new bug report or post on this one?
(In reply to GerardF from comment #30) > @ Winfried, > > I find a bug (or may be limitation) with TEXTJOIN, should I open a new bug > report or post on this one? @GerardF: If The TEXTJOIN in Calc differs from the TEXTJOIN functionality in Excel, please post in this bug report. This bug report is still open and active. Please also give the function call you use and the results in both Excel and Calc, preferably with an original Excel document as attachment, so that I can test and possibly use your particular case for automated tests.
(In reply to Winfried Donkers from comment #31) > If The TEXTJOIN in Calc differs from the TEXTJOIN functionality in Excel, > please post in this bug report. This is not really the case. I don't have Excel and it is not an import/export bug. Plus some functions like INDEX (or OFFSET, *LOOKUP) behaves differently in Excel and Calc, so I don't expect my formula to works in Excel. So I open a new bugreport report : https://bugs.documentfoundation.org/show_bug.cgi?id=99625
(In reply to GerardF from comment #28) > Is SWITCH fonction accept ranges arguments (in Excel, in Calc)? > Example : > =SWITCH(C1,A1:A7,B1:B7) > with > From A1 to A7 : 1 to 7 > From B1 to B7 : Sunday to Saturday Currently, Calc doesn't. Unfortunately I'm not in a position to test with Excel at short term, so I can't tell you yet if that's a shortcoming of Calc or a non-supported use of the function. The function as described in https://support.office.com/en-us/article/SWITCH-47ab33c0-28ce-4530-8a45-d532ec4aa25e does not mention supporting this use case. I hope to give a definite answer soon.
Winfried Donkers committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=29433c6496e8aa2d82ce56731d4bb734538a9f80 tdf#97831 [part] Add Excel 2016 functions to Calc It will be available in 5.2.0. 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 "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=a2aea8bac55cef23297573733ba28f563f5aa791 IFS/SWITCH propagate error only for active paths, tdf#97831 follow-up It will be available in 5.2.0. 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.
The above latest patch implements error propagation of parameters like I *think* it could be correct. Could someone with access to Excel-365 or alike check what that returns for =IFS(0,1/0,1,2) Calc now returns 2 =IFS(1/0,1/0,1,2) Calc now returns #DIV/0! =IFS(0,1/0,1/0,2) Calc now returns #DIV/0! =SWITCH(0,1/0,2,0,4) Calc now returns 4 =SWITCH(0,1/0,2,1/0,4) Calc now returns #DIV/0! =SWITCH(0,1/0,2,1/0,4,5) Calc now returns 5
(In reply to GerardF from comment #28) > Is SWITCH fonction accept ranges arguments (in Excel, in Calc)? > Example : > =SWITCH(C1,A1:A7,B1:B7) > with > From A1 to A7 : 1 to 7 > From B1 to B7 : Sunday to Saturday In Calc it currently does the same as for other functions where a parameter expects a scalar argument, an implicit intersection of the range and the formula cell position is created. So if you enter the formula above in D3, the intersection with A1:A7 is A3, the intersection with B1:B7 is B3. Which means that only for the value 3 in C1 a result is returned, other values give #N/A. If entered as an array/matrix formula (Shift+Ctrl+Enter) the result will be a matrix of 7 values, where one element is the matching case string, the other 6 are #N/A values. Someone with access to Excel-365 should check its behavior regarding these conditions.
Winfried Donkers committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=a5a731daec8dcb4f44bb618b71e6995ec1deb561 tdf#97831 follow up : use ScInterpreter::ReverseStack() It will be available in 5.2.0. 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 "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=c57dc741a6f221f53f3c8da2f521c6ece63246c2 tdf#97831 [part] Add Excel 2016 functions to Calc It will be available in 5.2.0. 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 "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=cbb6367e75e3cc8b5b2b6f2ed703e5a316d8f325 gather calls to IterateParametersIfs() in one place, tdf#97831 follow-up It will be available in 5.2.0. 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 "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=c54f6161480fc3721bf8e208cd12a7446ab608b7 coverity#1359230 DEADCODE, detect missing parameter, tdf#97831 follow-up It will be available in 5.2.0. 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 "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=2844f3114c2a7b122fe299ed21ed06ca57866e47 actually propagate a match expression error, tdf#97831 follow-up It will be available in 5.2.0. 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 Eike Rathke from comment #36) > =IFS(0,1/0,1,2) > Calc now returns 2 right > =IFS(1/0,1/0,1,2) > Calc now returns #DIV/0! right > =IFS(0,1/0,1/0,2) > Calc now returns #DIV/0! right OUCH. I have found the correct translation from SWITCH. Excel Online seem to got another new translation. Moreover the translation of the command did not change in the help. Sooo, I will provide in a few minutes the working stuff. Here At least here the answers to Eike's questions. Oh BTW: in German SWITCH seems to be "ERSTERWERT" -.- > =SWITCH(0,1/0,2,0,4) > Calc now returns 4 #DIV/0 > =SWITCH(0,1/0,2,1/0,4) > Calc now returns #DIV/0! #DIV/0 > =SWITCH(0,1/0,2,1/0,4,5) > Calc now returns 5 #DIV/0
Created attachment 124889 [details] SWITCH.xslx
Created attachment 124890 [details] SWiTCH.ods
(In reply to Eike Rathke from comment #37) > (In reply to GerardF from comment #28) > > Is SWITCH fonction accept ranges arguments (in Excel, in Calc)? > > Example : > > =SWITCH(C1,A1:A7,B1:B7) > > with > > From A1 to A7 : 1 to 7 > > From B1 to B7 : Sunday to Saturday > > In Calc it currently does the same as for other functions where a parameter > expects a scalar argument, an implicit intersection of the range and the > formula cell position is created. So if you enter the formula above in D3, > the intersection with A1:A7 is A3, the intersection with B1:B7 is B3. Which > means that only for the value 3 in C1 a result is returned, other values > give #N/A. > > If entered as an array/matrix formula (Shift+Ctrl+Enter) the result will be > a matrix of 7 values, where one element is the matching case string, the > other 6 are #N/A values. > > Someone with access to Excel-365 should check its behavior regarding these > conditions. Hi Eike, can you provide me a test document? I'm not that familiar to produce such documents and moreover using the Excel online version I'm really helpless lost to find any stuff... Regards, Dennis
@Dennis: I somehow missed your comment, I'll come up with some test cases. Btw, what does Excel return for =SWITCH(1,1,11,1/0,22) IMHO should be 11, which Calc currently does, but if all arguments are evaluated (i.e. function does not behave as a jump function like IF) and any error is propagated it could be #DIV/0! as well.
Winfried Donkers committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=1e3b7cbff66eb81a008eca03b56fcd128ddbe45f SWITCH returns error if compared value contains an error, tdf#97831 follow-up It will be available in 5.2.0. 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 Eike Rathke from comment #47) > Btw, what does Excel return for =SWITCH(1,1,11,1/0,22) In Excel Online it returns 11 :-)
Winfried Donkers committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=908bcbc183a720143e7525ae3e825ff7719192ab tdf#97831 unit test document for new Excel2016 functions It will be available in 5.2.0. 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 125332 [details] some test cases, already mentioned in comments, .ods
Created attachment 125333 [details] some test cases, already mentioned in comments, .xlsx
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=ab7e112bcf2ecd09ea129ef81177df8036110cb6 fix obtaining ScRefCellValue results, tdf#97831 follow-up It will be available in 5.3.0. 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.
It's unclear to me what sc/qa/unit/data/functions/fods/Functions_Excel_2016.fods actually tested in the last three tests, our behavior that IMHO was wrong, or Excel behavior. So what does Excel-365 on an otherwise blank sheet return for in A1 entered =SWITCH(B1,C1:C3,D1:D3) ? We now return 0, as empty cells usually evaluate to 0, the test assumed #N/A which we previously returned.
Created attachment 125645 [details] screencopy of Excel Online (In reply to Eike Rathke from comment #54) > It's unclear to me what > sc/qa/unit/data/functions/fods/Functions_Excel_2016.fods actually tested in > the last three tests, our behavior that IMHO was wrong, or Excel behavior. > So what does Excel-365 on an otherwise blank sheet return for in A1 entered > =SWITCH(B1,C1:C3,D1:D3) ? We now return 0, as empty cells usually evaluate > to 0, the test assumed #N/A which we previously returned. The last seven tests of SWITCH return the same result (six times #N/A and one 'tue') as Excel does, as the attachment shows. (Even though ExcelOnline has huge disadvantages, for simply comparing formula results with Calc, it's great and free to use.)
Created attachment 125646 [details] screencopy of Excel Online (2) (In reply to Winfried Donkers from comment #55) > Created attachment 125645 [details] > screencopy of Excel Online > > (In reply to Eike Rathke from comment #54) > > It's unclear to me what > > sc/qa/unit/data/functions/fods/Functions_Excel_2016.fods actually tested in > > the last three tests, our behavior that IMHO was wrong, or Excel behavior. > > So what does Excel-365 on an otherwise blank sheet return for in A1 entered > > =SWITCH(B1,C1:C3,D1:D3) ? We now return 0, as empty cells usually evaluate > > to 0, the test assumed #N/A which we previously returned. This time the screen copy reflects your description, but do note the difference in return values between attachment 125645 [details] and this attachment.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=32ecf73b9f26e74f72086bf43a72244c165a391c&h=libreoffice-5-2 fix obtaining ScRefCellValue results, tdf#97831 follow-up It will be available in 5.2.0.1. 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 Winfried Donkers from comment #56) > > > So what does Excel-365 on an otherwise blank sheet return for in A1 entered > > > =SWITCH(B1,C1:C3,D1:D3) ? We now return 0, as empty cells usually evaluate > > > to 0, the test assumed #N/A which we previously returned. > > This time the screen copy reflects your description, Good, so the changed unit test document reflects Excel reality now. > but do note the > difference in return values between attachment 125645 [details] and this > attachment. Of course, the former has cell content in the ranges referenced and thus values to be matched, the latter references empty cells and thus matches 0 of another empty cell.
Created attachment 126450 [details] SWITCH in math formula.ods I tested version 5.2.0.1 (from Launchpad PPA) today, but it seems that SWITCH function cannot be used in more complex formulae. For example: =SWITCH(A6;A1;A2;B1;B2;C1;C2) gives 2, but =1/SWITCH(A6;A1;A2;B1;B2;C1;C2) gives an error (except if the value chosen by SWITCH is the last argument; C2 in this case). Another example: =SWITCH(A6;A1;A2;B1;B2;C1;C2)*2 gives 4, but =2*SWITCH(A6;A1;A2;B1;B2;C1;C2) gives an error (with the same exception as above). See attachment SWITCH in math formula.ods
(In reply to Quentin Henriet from comment #59) > Created attachment 126450 [details] > SWITCH in math formula.ods > > I tested version 5.2.0.1 (from Launchpad PPA) today, but it seems that > SWITCH function cannot be used in more complex formulae. Thank you for your report; I have reopened this bug report and will work on it right now.
(In reply to Quentin Henriet from comment #59) > SWITCH in math formula.ods Similar problem with IFS. I found the cause and am now focussing on the fix.
Winfried Donkers committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=3ee2cae3a36e5d052e02c3f7b226dc3b79bd2ebc tdf#97831 follow up : remove unused arguments from stack It will be available in 5.3.0. 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-5-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=e43e2886effea42658f4bc3b69f46cc70c9251e0&h=libreoffice-5-2 tdf#97831 follow up : remove unused arguments from stack It will be available in 5.2.1. 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.
I hope there is a specification of the functions concerned in a reworked document I not yet know. If not so, the new functions should not become part of a release in advance of having created sound specifications. PLEASE point me to the specifications if any! Unspecified functions are a nightmare we should lightheartedly leave to the commercial competitors. A specification just saying "as Excel does" is the ultimate nightmare. What version of Excel? What the next year? See also bug#101134 and bug#101137 .
(In reply to Wolfgang Jäger from comment #64) These functions have been added for interoperability with Excel. They are essentially Excel functions that are offered in Calc too. The specifications of these Excel functions (see comment #0) are sketchy, I cannot help that. It is important for Calc to be interoperable with Excel. That means we have to use the information regarding how Excel functions work that is available, plus use Excel to find out the function's behaviour in various use cases. The bug reports you refer to, concern use as array function. That is a use case that is not (fully) supported by CONCAT and TEXTJOIN (in both Excel and Calc), see bug https://bugs.documentfoundation.org/show_bug.cgi?id=99625#c2. One could argue whether using these functions as array function has any realistic use. If you think the documentation of one or more Calc functions is insufficient, please create a separate bug report for this.
(In reply to Winfried Donkers from comment #65) > ... > It is important for Calc to be interoperable with Excel. > ... > The bug reports you refer to, concern use as array function. ... I do not object to the statement that interoperability with Excel is relevant. More relevant is, however, that mandatory specifications for ODF are obeyed as long as they are in effect. Among those: Behaviour of spreadsheet functions concerning the evaluation of parameters and the types of the results to return. As the parameter(s) introducing the data of the CONCAT and of the TEXTJOIN function cannot be scalar concerning their purposes, an evaluation in iterative mode cannot occur with CONCAT, and also not with TEXTJOIN as far as the parameter for the contributed texts is concerned. It is mandatory, on the other hand, for TEXTJOIN with respect to the parameter introducing the delimiter (and also for the skip-empty parameter). And it is necessary to evaluate formulae in place of the parameters as is specified. The EVALUATOR should provide means! No need to do that for each function separately should be. > ... That is a use > case that is not (fully) supported by CONCAT and TEXTJOIN (in both Excel and > Calc), see bug https://bugs.documentfoundation.org/show_bug.cgi?id=99625#c2. > One could argue whether using these functions as array function has any > realistic use. ... > ... Missed the point. The parameters (1 or more) of CONCAT and those of TEXTJOIN from the third position rightwards must be seen in analogy of the parameters of SUM which are specified to be of type NumberSequence. To make possible at all a consistent specification of CONCAT and TEXTJOIN we have first to assume an as-if specification of type TextSequence the (State of ODF 1.2: still missing) first. (Analogy again! ComplexSequence already exists, Complex being implemented as specific kind of text.) How to evaluate a parameter of a Sequence type if an array is in place of it is specified! It MUST be done for TextSequence in the same way to avoid inconsistency of Calc on the large scale. > ... > If you think the documentation of one or more Calc functions is > insufficient, please create a separate bug report for this. > ... The above said regarded, we may START with a specification of the mentioned functions for Calc. There is NONE, as far as I know. Starting with an implementation not bound to a specification is the original sin of programming. If I am wrong and there is a probably insufficient specification, please point me to it! Then I can create a separate bug report for it. If we want to be compatibel with something, we have, of course, to do research about that something. If things go well we will specify our new functions in a way ensuring the intended compatibility. If things go not so good, we may have to accept some flaws in compatibility. Under no circumstances we should sacrifice obedience to mandatory specifications of OUR software for the sake of compatibility, or omit a specification of a new function at all. Do it "as Excel does" is no specification at all. A strategy of "Compatibility with MS Office overrules it all!" may be thought to be helpful with respect to acceptance. It will not succeed. It is the way into complete surrender, and competitors are well aware of the fact that it is in their power to create new incompatibilities every next day. In case of someone feeling my comment off topic again, continued discussion can be via email. (Discussion in German there appreciated.)