https://bugs.documentfoundation.org/show_bug.cgi?id=96426
Bug ID: 96426
Summary: [1] space as intersection in Excel A1 formula syntax
gives Err:509 or Err:508
Product: LibreOffice
Version: 5.0.3.2 release
Hardware: x86-64 (AMD64)
OS: Linux (All)
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
==Summary==
The operator for the intersection of two ranges is '!' in Calc A1 formula
syntax and ' ' <SPACE> in Excel A1 formula syntax. With the appropriate formula
syntax selected, a range intersection using '!' in Calc A1 syntax works, but a
range intersection using <SPACE> in Excel A1 syntax gives an error.
If the ranges are specified indirectly as "names", and there are parenthesis
round the operation, the error is Err:508 (Pair missing). With no parentheses
it gives Err:509 (missing operator).
If the ranges are specified in Excel A1 syntax (not as "names"), the error is
always Err:509 (missing operator), irrespective of whether there are
parentheses.
This is a bug, because the formula with a space for an intersection operator is
perfectly valid Excel A1 formula syntax, so an Error code is an incorrect
output.
NB: Nothing in this bug is about import/export from MS Excel. It is solely
about the Excel A1 formula syntax within native Calc. As long as the formulae
are entered in Calc, the bug is independent of which format the sheet is saved
in (it is even reproducible before saving the file).
The following examples are available with screen-shots added here:
<http://homefarmparham.co.uk/tmp/calc/IntersectCalcBug.html>
==Expected Behaviour==
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.
==Actual Behaviour==
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)
If the ranges are defined as names (using Insert>Names>Define), e.g.
horiz $2:$2
vert $B:$B
then an intersection formula is defined with parentheses around these names,
for example:
=(horiz vert)*2
it returns "Err:508" (pair missing). However, a similar formula without
parentheses such as
=horiz vert
returns "Err:509" (missing operator)
==Absence of Any Intersection Operator in Excel A1 Formula Syntax==
Incidentally, with Excel A1 Formula Syntax selected, attempting to use '!' as
an intersection operator correctly gives a #NAME? error. So there is no working
intersection operator at all in Calc's Excel A1 Formula Syntax.
==System build==
Libre Office
Version: 5.0.3.2
Build ID: 1:5.0.3~rc2-0ubuntu1~trusty2
Locale: en-GB (en_GB.UTF-8)
--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Libreoffice-bugs mailing list
[email protected]
http://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
