Can someone with access to Excel 2016 test as described in the attachment, please?

[Lupp]

(No further bumps, hopefully. The issue was handled due to a thankworthy intervention of a contributor to another forum that actually deserves this name. He was also the original reporter of the mentioned bug.)
See LibO 5.2 or higher: TEXJOIN and CONCAT behaviour (View topic) • Apache OpenOffice Community Forum .
See also https://bugs.documentfoundation.org/show_bug.cgi?id=99625#c22.

(2. Bump. There are also new comments to be regarded here.)
To everybody having access to Excel 2016 and in specific to @rautamiekka.
I would like to ask again for testing an example. To simplify things a bit I now do it based on an example, one Alan Murray (MS software trainer and founder of the “Computergaga”) posted on YouTube as of 2016-11-02.
See this demonstrating file.
In case of problems like those @rautamiekka already experienced, I would suggest to re-enter the critical formulae via the keyboard. Please note that only Excel 2016 can be used for this. Older versions won’t do, and a cloud implementation may also differ.
@karolus is right with his comment as far as the facts are concerned. He did not know the comments on the bug tdf#99625 by developers I linked in later.

(1. Bump. Also regard my answering comment to the comment by @rautamiekka, please.)

There is a saying that Excel 2016 has implemented two new functions, namely CONCAT and TEXTJOIN capable of accumualting an arbitrary number of text pieces where these pieces may be listed one by one (as also possible using CONCATENATE), but also can be given as the contents of referenced cell ranges.
The second variant can be used to solve some tasks formerly depending on complicated usage of helper columns or even on user programming, now in a handy way. To get the full power out of these functions we need, of course, to be able to pass calculated arrays instead of simple ranges via the respective parameters. To do so, a formula will need to be entered explicitly for array-evaluation.
A simple example:
{=CONCAT(IF(A1:A10<5;B1:B10;"")} returning the concatenation of those elements of B1:B10 that correspond with values < 5 in A1:A10, and leaving out the other elements.

Handling an enhancement request (tdf#97831) these functions were also implemented in LibO Calc as of V5.2.0.
As I was told, “There are no specifications, as these functions were introduced to be compatible with Excel’s new functions, …”. I still do not understand how this can justify an implementation without first stating which behaviour of the Excel functions was actually explored and now is to reproduce. In fact we have the mess that obviously nobody knows which behaviour actually was observed and that there are contradicting opinions about how it should be in the cases where array-evaluation is needed. Since 2016-05-02 when a respective bug was filed nobody was able to clear the issue. Currently the concerned developers obviously have no access to Excel 2016 for testing.

Might, please, someone having access to Excel 2016 transfer the content of this attached example to a spreadsheet made by that software, and test if the behaviour in Excel (2016) actually is the same as in Calc (>=V5.2.0) or in what way it differs.

See also: tdf#99625, tdf#101124 (meanwhile marked as duplicate).

[karolus]

IMHO stellst du hier die falsche Frage!?
Wenn Excel2016 sich im Matrixkontext genauso verhält, ist die Funktion im gegebenen Fall dort ebenso nutzlos! Wenn nicht, ist die Implementierung in LO wohl suboptimal. →→

Das eigentliche Problem (der Implementierung ) dahinter ist vmtl. die nicht eindeutig lösbare Auflösung der optionalen (ein __oder__ mehrerer ) Bereiche.

[Lupp]

Hallo @karolus, mein Prtoblem ist der Zustand, nach den Kommentaren tdf#99625#c13 und tdf#99625#c16: Es bewegt sich nichts.
Allem Anschein nach liegt der m.E. unerträgliche Fall vor, dass einerseit “Excel 2016 nachbilden” die einzige Leitlinie für die Implementierung zweier Funktionen ist, und andererseits der Entwickler gar keinen Zugriff auf Excel 2016 hat.

[Lupp]

… Personally I am not concerned. I simply was long aware of the power of a pair of functions: one capable of accumulating text pieces and a pendant capable of chopping texts in a reverse way. Thus I had written this pair (yes, in BASIC), My xTextJoin is more powerful, and it is paired with xTextSplit still missing functionally in Calc and in Excel as well.I just slightly adapted the parameter interpretation for better near-compatibility.
My interest now is the enhancement of free SW.

[rautamiekka]

Yeah, Excel 2016 does produce #NAME? in each cell with a formula in the I column, and the formulas are like =COM.MICROSOFT.TEXTJOIN(";",0,A1:A3&G1:G3,C1:C5&E1:E5), with leading { and trailing }. Likewise if I open the XLSX created by Excel 2016 in LO, those unknown formulas produce #NAME?.

[Lupp]

@rautamiekka: Thanks a lot! Did you also try to delete the “COM.MICROSOFT.” prefix (or a respective one occurring with the reverse migration)? The prefix is specific to Calc, of course, though it is only written to the file and not shown while the document is open in Libo.

[rautamiekka]

No difference with removing COM.MICROSOFT.. The ODS saved as XLSX does change the function names, but in the end it’s the good old #NAME?. Screenshots: http://imgur.com/a/HVSEE

[Lupp]

Thanks @rautamiekka: But there is
https://bugs.documentfoundation.org/show_bug.cgi?id=97831 ,
https://bugs.documentfoundation.org/show_bug.cgi?id=99517 ,
and (e.g. this garrulous video: https://www.youtube.com/watch?v=NejIt6JUDJE
Everything just spectres?
I am going to go crazy about this gradually. For my actual applications I have more powerful user functions. Don’t need the MS imitations. But I invested some hours now in reporting and commenting on related ghost-bugs. Big Mess!

[Lupp]

Is Alan Murray who published the video linked above in YouTube the only person in the world having access to the “true” Excel 2016? Probably only on 2016-11-02?
I made a contact with a software distributor I know from former times. They had just delisted Office 2016 denying its profitable salability (with respect to the needed support). Their own system was already cleaned…
What good with debugging sold software if it promotes as is its cloud sister I can sell again and again?

[erAck]

Note that the CONCAT and TEXTJOIN functions are only available in Excel 2016 with an Office 365 subscription, the plain Excel 2016 desktop version does not suffice, so of course it produces a #NAME? error when loading such. And of course it will accept the functions only as they are stored in .xlsx, not in .ods

[erAck]

Meanwhile the behaviour has been fixed to return one string instead of an array of strings when in array context, see tdf#99625