Bug 97831 - Please add new functions IFS, MAXIFS, MINIFS, SWITCH, CONCAT, TEXTJOIN (new in Excel 2016 Jan16)
Summary: Please add new functions IFS, MAXIFS, MINIFS, SWITCH, CONCAT, TEXTJOIN (new ...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Winfried Donkers
QA Contact:
URL:
Whiteboard: needsExcel target:5.2.0 target:5.3.0 ...
Keywords:
Depends on:
Blocks: HelpGaps 99625
  Show dependency treegraph
 
Reported: 2016-02-13 10:43 UTC by Gerry
Modified: 2016-10-22 21:41 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
CONCAT.ods (2.89 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-03-04 20:01 UTC, Dennis Roczek
Details
concat.xslx (7.87 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-03-04 20:01 UTC, Dennis Roczek
Details
better CONCAT2 example (8.54 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-03-05 20:21 UTC, Dennis Roczek
Details
ifs.ods (2.93 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-03-05 20:21 UTC, Dennis Roczek
Details
ifs.xslx (8.11 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-03-05 20:22 UTC, Dennis Roczek
Details
minifs.ods (3.00 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-03-05 23:14 UTC, Dennis Roczek
Details
minifs.xlsx (8.19 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-03-05 23:14 UTC, Dennis Roczek
Details
MAXIFS.ods (3.00 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-03-05 23:14 UTC, Dennis Roczek
Details
MAXIFS.ods (8.18 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-03-05 23:15 UTC, Dennis Roczek
Details
TEXTJOIN.ods (3.03 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-03-06 00:19 UTC, Dennis Roczek
Details
TEXTJOIN.xlsx (8.66 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-03-06 00:20 UTC, Dennis Roczek
Details
SWITCH.xslx (8.32 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-05-07 01:45 UTC, Dennis Roczek
Details
SWiTCH.ods (2.99 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-05-07 01:45 UTC, Dennis Roczek
Details
some test cases, already mentioned in comments, .ods (15.05 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2016-05-27 17:55 UTC, Eike Rathke
Details
some test cases, already mentioned in comments, .xlsx (6.20 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-05-27 17:56 UTC, Eike Rathke
Details
screencopy of Excel Online (15.31 KB, image/gif)
2016-06-14 06:27 UTC, Winfried Donkers
Details
screencopy of Excel Online (2) (4.97 KB, image/gif)
2016-06-14 07:01 UTC, Winfried Donkers
Details
SWITCH in math formula.ods (9.74 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-07-28 14:35 UTC, Quentin Henriet
Details

Note You need to log in before you can comment on or make changes to this bug.
Comment 1 Winfried Donkers 2016-02-18 15:29:00 UTC
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.
Comment 2 Winfried Donkers 2016-02-18 16:21:22 UTC
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 ?
Comment 3 Winfried Donkers 2016-03-04 08:37:22 UTC
@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.
Comment 4 Gerry 2016-03-04 10:14:19 UTC
@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.
Comment 5 Dennis Roczek 2016-03-04 10:55:18 UTC
I might have, will test them tomorrow. Do you need test documents?
Comment 6 Winfried Donkers 2016-03-04 11:42:19 UTC
(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).
Comment 7 Dennis Roczek 2016-03-04 20:01:19 UTC
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
Comment 8 Dennis Roczek 2016-03-04 20:01:42 UTC
Created attachment 123299 [details]
concat.xslx
Comment 9 Dennis Roczek 2016-03-05 20:21:45 UTC
Created attachment 123318 [details]
better CONCAT2 example
Comment 10 Dennis Roczek 2016-03-05 20:21:58 UTC
Created attachment 123319 [details]
ifs.ods
Comment 11 Dennis Roczek 2016-03-05 20:22:14 UTC
Created attachment 123320 [details]
ifs.xslx
Comment 12 Dennis Roczek 2016-03-05 23:14:33 UTC
Created attachment 123323 [details]
minifs.ods
Comment 13 Dennis Roczek 2016-03-05 23:14:47 UTC
Created attachment 123324 [details]
minifs.xlsx
Comment 14 Dennis Roczek 2016-03-05 23:14:59 UTC
Created attachment 123325 [details]
MAXIFS.ods
Comment 15 Dennis Roczek 2016-03-05 23:15:10 UTC
Created attachment 123326 [details]
MAXIFS.ods
Comment 16 Dennis Roczek 2016-03-06 00:19:47 UTC
Created attachment 123329 [details]
TEXTJOIN.ods
Comment 17 Dennis Roczek 2016-03-06 00:20:02 UTC
Created attachment 123330 [details]
TEXTJOIN.xlsx
Comment 18 Dennis Roczek 2016-03-06 00:39:19 UTC
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. ;-)
Comment 19 Winfried Donkers 2016-03-06 11:57:49 UTC
(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.
Comment 20 Olivier Hallot 2016-04-05 17:01:28 UTC
Please consider documenting these function for the help content.
Comment 21 Winfried Donkers 2016-04-06 06:08:22 UTC
(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?
Comment 22 Commit Notification 2016-04-26 15:11:19 UTC
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.
Comment 23 Commit Notification 2016-04-26 16:08:40 UTC
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.
Comment 24 Commit Notification 2016-04-26 16:27:06 UTC
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.
Comment 25 Commit Notification 2016-04-29 09:14:01 UTC
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.
Comment 26 Dennis Roczek 2016-04-30 23:34:18 UTC
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...
Comment 27 Winfried Donkers 2016-05-02 06:03:23 UTC
(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?
Comment 28 GerardF 2016-05-02 06:26:48 UTC
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
Comment 29 Dennis Roczek 2016-05-02 08:14:16 UTC
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.
Comment 30 GerardF 2016-05-02 08:21:43 UTC
@ Winfried,

I find a bug (or may be limitation) with TEXTJOIN, should I open a new bug report or post on this one?
Comment 31 Winfried Donkers 2016-05-02 10:12:17 UTC
(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.
Comment 32 GerardF 2016-05-02 11:46:16 UTC
(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
Comment 33 Winfried Donkers 2016-05-02 15:45:08 UTC
(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.
Comment 34 Commit Notification 2016-05-03 16:12:28 UTC
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.
Comment 35 Commit Notification 2016-05-03 17:18:21 UTC
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.
Comment 36 Eike Rathke 2016-05-03 17:54:53 UTC
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
Comment 37 Eike Rathke 2016-05-03 22:04:06 UTC
(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.
Comment 38 Commit Notification 2016-05-04 10:28:30 UTC
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.
Comment 39 Commit Notification 2016-05-04 21:56:00 UTC
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.
Comment 40 Commit Notification 2016-05-04 22:10:22 UTC
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.
Comment 41 Commit Notification 2016-05-06 23:09:40 UTC
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.
Comment 42 Commit Notification 2016-05-06 23:09:44 UTC
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.
Comment 43 Dennis Roczek 2016-05-07 01:35:45 UTC
(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
Comment 44 Dennis Roczek 2016-05-07 01:45:00 UTC
Created attachment 124889 [details]
SWITCH.xslx
Comment 45 Dennis Roczek 2016-05-07 01:45:22 UTC
Created attachment 124890 [details]
SWiTCH.ods
Comment 46 Dennis Roczek 2016-05-07 01:59:00 UTC
(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
Comment 47 Eike Rathke 2016-05-25 11:20:41 UTC
@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.
Comment 48 Commit Notification 2016-05-25 11:36:49 UTC
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.
Comment 49 Winfried Donkers 2016-05-25 14:08:47 UTC
(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 :-)
Comment 50 Commit Notification 2016-05-26 02:43:20 UTC
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.
Comment 51 Eike Rathke 2016-05-27 17:55:35 UTC
Created attachment 125332 [details]
some test cases, already mentioned in comments, .ods
Comment 52 Eike Rathke 2016-05-27 17:56:13 UTC
Created attachment 125333 [details]
some test cases, already mentioned in comments, .xlsx
Comment 53 Commit Notification 2016-06-13 16:40:32 UTC
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.
Comment 54 Eike Rathke 2016-06-13 17:11:03 UTC
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.
Comment 55 Winfried Donkers 2016-06-14 06:27:59 UTC
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.)
Comment 56 Winfried Donkers 2016-06-14 07:01:09 UTC
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.
Comment 57 Commit Notification 2016-06-14 08:04:14 UTC
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.
Comment 58 Eike Rathke 2016-06-14 13:42:26 UTC
(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.
Comment 59 Quentin Henriet 2016-07-28 14:35:35 UTC
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
Comment 60 Winfried Donkers 2016-07-28 15:31:35 UTC
(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.
Comment 61 Winfried Donkers 2016-07-29 09:20:19 UTC
(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.
Comment 62 Commit Notification 2016-07-29 16:45:10 UTC
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.
Comment 63 Commit Notification 2016-07-29 18:32:58 UTC
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.
Comment 64 Wolfgang Jäger 2016-08-02 17:46:36 UTC
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 .
Comment 65 Winfried Donkers 2016-08-03 05:54:14 UTC
(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.
Comment 66 Wolfgang Jäger 2016-08-03 11:02:16 UTC
(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.)