Calc A1 Formula Syntax Selected
|
Excel A1 Formula Syntax Selected
|
With Calc A1 Formula Syntax, the '!' operator in a formula gives the
intersection of two ranges. For example a cell containing the formula:
=($B:$B!$2:$2)*2
should resolve to 28, if the contents of B2 is the number 14. |
With Excel A1 Formula Syntax (selected via Tools>Options>Formula),
the <space> operator in a formula is meant to be equivalent,
giving the intersection of two ranges. For example:
=($B:$B $2:$2)*2
should be equivalent. |
|
|
With Calc A1 Formula Syntax selected, when typing the formula, Calc
displays the two ranges as intersecting coloured boxes
around the ranges and it colours the characters in the formula to match.
On pressing <ENTER>, it correctly displays 28.
|
With Excel A1 Formula Syntax selected, when typing the formula, Calc
displays the two ranges as expected; as intersecting coloured boxes
around the ranges and it colours the characters in the formula to match.
However, on entering the formula (type <ENTER> or click the
tick), the cell displays "Err:509" (missing operator)
|
 |
 |
|
The above variant of the bug was FIXED in version 5.2.0
But the following variant of the bug persists in at least:
LibreOffice
Version 5.2.3.2
Build ID: 1:5.2.3~rc2-0ubuntu1~trusty1
Locale: en-GB (en_GB.UTF-8)
|
If the ranges are defined as names (using Insert>Names>Define),...
|
When Excel A1 Formula Syntax is selected, the
syntax of the names correctly auto-transforms to the appropriate syntax
($Sheet1. becomes Sheet1!), ...
|
|
|
... and the result is still correct, 28.
|
... however, the result this time is "Err:508" (pair missing)
|
|
|
In Calc Formula Syntax, the intersection operator works fine without parentheses
|
Nonetheless, in Excel Formula Syntax, if there
are no parentheses in the formula, you get "Err:509" (missing operator),
rather than "Err:508" (pair missing).
|
|
|
|
Incidentally, using the '!' operator if Excel A1 Formula Syntax is selected correctly gives a #NAME? error.
|