LibO 5.2 or higher: TEXJOIN and CONCAT behaviour

Discuss the spreadsheet application
Post Reply
User avatar
Lupp
Volunteer
Posts: 3535
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

LibO 5.2 or higher: TEXJOIN and CONCAT behaviour

Post by Lupp »

Can someone with access to Excel 2016 test as described in this thread of the askbot site mimicking an official forum for LibreOffice:
https://ask.libreoffice.org/en/question/91825 .
The example attached there under the "2. bump" also contains a link to a youtube explanation concerning the two mentioned functions which were introduced with Excel 2016 first and then also implemented in LibreOffice as of V5.2.
There is a bug report tdf#99625 (https://bugs.documentfoundation.org/sho ... i?id=99625) complaining that the behaviour of both the mentioned functions in cases where a parameter is calculated under array evaluation is wrong. Another claim (as I understand it) is that the behaviour is the same as in Excel 2016. Since there is no specification of the functions in OpenFormula, compatibility with Excel 2016 is said to be the only raison d'etre of the new functions.

The discussion of the bug stopped, and I did not get reliable results from the above linked askbot thread. Thus I try it here now. I also tried to get access to an Excel 2016 for testing myself, but I failed.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
gerard24
Volunteer
Posts: 958
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

Re: LibO 5.2 or higher: TEXJOIN and CONCAT behaviour

Post by gerard24 »

Hello, I'm the bug reporter.

Developers persist in denying evidence. A result of formula returning an array should be treated as an array and not as many arrays of single cells like any other function.
I don't have Excel in order to test but I've found some examples of TEXTJOIN in array context :

http://www.get-digital-help.com/2016/06 ... -function/
or
https://www.youtube.com/watch?v=QJ2O07EB80Q

And finally a spreadsheet with examples of TEXTJOIN array formula : https://people.highline.edu/mgirvin/You ... -1311.xlsx (just found yet with the link provided in the video)
LibreOffice 6.4.5 on Windows 10
User avatar
Lupp
Volunteer
Posts: 3535
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: LibO 5.2 or higher: TEXJOIN and CONCAT behaviour

Post by Lupp »

Thank you very much for acting on this - and mainly for finding the way (the "magic" .xlsx) to break the tie concerning how the bug was (un)handled by the developers. It seemed rather ridiculous to me that a completely absurd behaviour of new functions was defended (in a sense) based on a supposed noncredible behaviour of Excel 2016. I was discouraged, however.

By the way: I will continue to prefer my own user function xTextJoin for the purpose which is more powerful (though not yet in the final state). In addition I have coded the corresponding xTextSplit function completing the group, and adding nice functionality when applied in a pair.

Another joke: The bad TEXTJOIN could well be applied with more than one array-evaluated parameters of different dimensions. In this case it chose the largest one to get the size of the output range.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply