Array formula / Calc 4.4.3.2

[blue_tiger300]

So I have this array formula

=SUM(1*RIGHT(A2:A6;LEN(A2:A6)-FIND(" ";A2:A6)))

,which works in Excel 2013, but doesn’t in Calc. I have tried a “hard recalc” with CTRL+SHIFT+F9, but it only shows “value#”. What am I missing here ?

some edit for readability by Karolus

Edit 25May: Thanks for your answers/comments so far. Sorry, my question was not too clear. The original formula is an array formula, I just accidentally copied it without the curly braces from the formula wizard. I also realize, that there are different ways of solving this, but why would that formula --not-- work in LO while it does in Excel (2013) and even Google docs. I would like to attach my file, but I don’t have enough Karma yet.
@Lupp: I have loaded your testfile, but both sides of the worksheet show “value#” instead of the right side just picking out the values. So i wonder if this is a Linux version problem (I am using LO on Kubuntu 14.04) or just a simple setup problem?!

Edit 25May 1100: Here is the Excel file - the supposed result from the array formula is 177. Tried my file also under Win7 / LO 4.4.3.2 but with the same result as under Kubuntu.Week5_arraytest.xlsx

===Amendment by @Lupp ===

I reported this as bug tdf#91605

[Lupp]

@blue_tiger300 The testfile was made

a) To demonstrate the actuall situation with regard to answer and comments by @karolus. As I accept (and guessed first anyway) that Calc should do an automatoc conversion in the case, I have to determine Calc’s behavior a bug.

b) To demonstrate a solution. You may call it a workaround. In my feeling the usage of “1*” or “0+” to trigger conversion smells like a workaround I had to use if no explicit conversion function existed.

“Karma” supplied.

[Lupp]

@blue_tiger300 again:The specifications Calc is bond to (only describing part of its functionality) do never refer to Excel as a standard. We shouldn’t do either. Excel is made with huge effort and expense. With some respect (efficiency of calculations, mainly marketing e.g.) this was seemingly successful. Under other main aspects Excel is a mess. Developing free software as a Clone would mean final surrender. Basic compatibility, however is a main goal. And bugs must be fought.

[blue_tiger300]

I would never refer to Excel as a standard. That snipped comes from an online course which is (unfortunately) full with the latest features from M$. So far I have been able to do all the assignments from my Linux box with LO, but I was surprised to see this rather simple formula fail.

[Lupp]

@blue_tiger You surely read my edits to my answer. It’s the same with your test file except that older versions of Calc cannot open XLSX. A great deal of the already mentioned replacement of “legacy code” was done by LibreOffice preparing the 4.x series. This “regression issue”, however was not yet present in Calc 4.1
My unchanged advice: Be as explicit as possible about your intentions using Calc. It’s rather unlikely that a dedicated conversion function is buggy. “Smart-ies” often are.

[Lupp]

You surely realised that the formula (which isn’t actually an array formula)

=SUM(1*RIGHT(A2:A6;LEN(A2:A6)-FIND(" ";A2:A6)))

is containing an expression multiplying the number 1 with the text (string) returned by the RIGHT function. @karolus just reminded me how this will be handled. As I don’t have Excel to my disposition: Might you please tell me how Excel defines the product of a number with a text?

Maybe the right parts of text the formula extracts are syntactically numbers and Excel performs an automatic conversion from text to numeric on behalf of the factor 1 and the operator “*” (looking like the beginning of an arithmetic expression) and can get a result that way.

There are good reasons not do do so. Calc, however, does it too. expects you to tell what you want in such a case. I would prefer using the VALUE() function. If I guessed correctly

{=SUM(VALUE(RIGHT(A2:A6;LEN(A2:A6)-FIND(" ";A2:A6))))}

should do.

Provide the relevant information and example data with your next question, please, if possible.

EDITING

@karolus I tried again and got the error again. Additional tests (so far only with 4.4.3.2) showed that the automatic conversion of txt which is syntactically numbers by a prefixed “1*” does not work with the array returned by the text expression here. Might you be so kind, please, to review the attached example? ask51087TestOfAutomaticConversionOnArrayEval.ods

EDITING2

LibreOffice Calc V3.6.5 and still AOO Calc V$.1.1, more relying on ‘legacy code’, would resolve the {=SUM( ...)} issue - but only in the context of the surrounding function. The parameter part would not deliver the correct result to a group of cells. … But this is getting part of a bug discussion and should proceed in the proper place.

My advice: Use the VALUE function for the necessary conversion.

[karolus]

@Lupp
I’ve test now – it seems in this case ( array-context ?? ) the implicit conversion doesn’t work – so lets state***:

 Explicit is better than implicit.

***see also Zen of Python second Statement

[karolus]

you have to close as Array-formula eg. with ctrl+shift+enter or with activating the [x]array-option in the Formula-wizard

@Lupp: the Mathoperation *1 does implicitly change the other part of Operation from Text to Value
edit: the implicit conversion doesnt work in this case, so change to the explicit Solution provided by @Lupp

[Lupp]

OK I suspected the 1* to achieve type conversion (as I sometimes use a “0+” in BASIC for the purpose). Trying it I got an error and assigned it to the wrong cause. I had wrongly included an empty cell into the range. However, to name things clearly (using VALUE in this case) shouldn’t be a bad idea.