We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

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

asked 2017-04-01 02:09:33 +0200

Lupp gravatar image

updated 2020-08-09 19:58:54 +0200

Alex Kemp gravatar image

(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 https://forum.openoffice.org/en/forum... .
See also https://bugs.documentfoundation.org/s....

(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 ... (more)

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-08-09 19:59:29.951179


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.

karolus gravatar imagekarolus ( 2017-04-01 10:11:00 +0200 )edit

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 gravatar imageLupp ( 2017-04-01 11:39:05 +0200 )edit

... 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.

Lupp gravatar imageLupp ( 2017-04-01 11:49:37 +0200 )edit

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?.

rautamiekka gravatar imagerautamiekka ( 2017-04-01 12:00:58 +0200 )edit

@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.

Lupp gravatar imageLupp ( 2017-04-01 12:21:34 +0200 )edit

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

rautamiekka gravatar imagerautamiekka ( 2017-04-10 14:13:53 +0200 )edit

Thanks @rautamiekka: But there is
https://bugs.documentfoundation.org/s... ,
https://bugs.documentfoundation.org/s... , and (e.g. this garrulous video: https://www.youtube.com/watch?v=NejIt...
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 gravatar imageLupp ( 2017-04-10 19:54:13 +0200 )edit

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?

Lupp gravatar imageLupp ( 2017-04-10 20:12:57 +0200 )edit

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 gravatar imageerAck ( 2017-04-20 22:48:50 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-04-20 22:36:07 +0200

erAck gravatar image

updated 2017-04-20 22:37:18 +0200

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

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2017-04-01 02:09:33 +0200

Seen: 269 times

Last updated: Apr 20 '17