-
Type: Improvement
-
Status: Open
-
Priority: Major
-
Resolution: Unresolved
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:None
The current text is
<quote>
Functions returning arrays are not eligible for implicit iteration. When evaluated in 'matrix' mode the {0;0}th element is used.
Note 7:
=SUM(INDIRECT({"A1";"A2"}) e.g. would produce the value in A1 when evaluated in array mode.
</quote>
Problems with the text itself
"Functions returning arrays": We should directly say "functions with return data type 'Array'". Returning an array because of implied iteration cannot be meant here.
"are not eligible for implicit iteration": Is confusing, we could directly state the rule "the {0;0}th element is used".
A proposal could be:
In case the function has return type Array (4.10.), the (0;0)th element of the non-scalar input is used in place of the non-scalar input.
Thereby the wording "(0;0)th element of the non-scalar input" needs improvement.
But the behavior of both LibreOffice and Excel is strange. Assume cell A1 has value2 and cell A2 has value 3.
=MUNIT(A1:A2) entered with Ctrl+Shift+Enter on an 4x3 output range results
in LibreOffice
1 0 #N/A #N/A
0 1 0 #N/A
{{ #N/A #N/A #N/A #N/A}}
and in Excel (with xlsx)
1 1 1 1
1 1 1 1
{{ #N/A #N/A #N/A #N/A}}
Google Sheets does not allow to determine the output range by the user, but sets it itself. Finishing a formula edit with Ctrl+Shift+Enter adds the function ArrayFormula to the expression. Thus Google Sheets uses the formula
=ArrayFormula(MUNIT(A1:A2))
and returns a 1col x 2row result
1
1
Planmaker from SoftMaker Suite does not know MUNIT.
Please test Gnumeric.
BTW, MUNIT is the only function from the spec I knew, that has return data type Array and a scalar as parameter. Of cause application might have own functions of this kind.
Following the rule "the {0;0}th element is used", I expect the result
1 0 #N/A #N/A
0 1 #N/A #N/A
{{ #N/A #N/A #N/A #N/A}}
The 'matrix'
1 0
0 1
is the intermediate result of using A1 instead of range A1:A2.
The #N/A are added because the result area is larger than the to be displayed area. That is rule 2.1.4.
Problems with the Note
Again use value 2 in A1 and value 3 in A2.
Expected behavior for =SUM(INDIRECT({"A1";"A2"}) in array mode:
As INDIRECT returns a reference, the rule 2.2.1 is not applicable.
Rule 2.2.2 determines, that the size of the result 'matrix' is 2col x 1row.
From the general text at start of list item 2.2, we know, we have to calculate INDIRECT("A1") and INDIRECT("A2").
That results in the references A1 and A2, respectively. These results are put into a result 'matrix' with one row and two columns. Problem: It is not clear, what we get as intermediate result here. => OFFICE-4166
SUM is excluded from array mode but always adds all the values it gets for its parameters. Thus the final result should be a single number in any case.
LibreOffice has the error, that the final output is not a single value but a cell rectangle of 1col and 2rows (bug 162530).
Excel produces a row with values 2 and 3 in Ctrl+Shift+Enter mode and value 5 in dynamic array mode.
PlanMaker (SoftMaker suite) produces a column with values 2 and 3 in Ctrl+Shift+Enter mode.
Google Sheets produces a single value 2.
Gnumeric ??