https://bugs.documentfoundation.org/show_bug.cgi?id=96429
Bug ID: 96429
Summary: Importing the Intersection (or Union) Operator from
Excel with Excel A1 (or R1C1) Formula Syntax
Product: LibreOffice
Version: 5.0.3.2 release
Hardware: x86-64 (AMD64)
OS: Linux (All)
Status: UNCONFIRMED
Severity: minor
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
A version of this bug-report with screen-shots is here:
<http://homefarmparham.co.uk/tmp/calc/IntersectCalcImportBug.html>
In Calc, if you had selected Calc A1 Formula Syntax (the default), then you
import a formula from MS Excel, an intersection operator that was <SPACE> in
Excel A1 formula syntax correctly converts to the '!' operator. For example, if
the formula in MS Excel on Windows was originally:
=A2:C2 B1:B3+3
then after import, the Calc A1 formula syntax will (correctly) be:
=A2:C2!B1:B3+3
For instance, if the number 7 is in cell B2, the output of the above formula
will be 10.
In Calc, if instead you had selected Excel A1 Formula Syntax (using
Tools>Options>Calc>Formula), then you import a sheet from MS Excel, an
intersection operator that was <SPACE> in Excel should appear unchanged as a
<SPACE>. However, it incorrectly displays as the '!' operator as if it has
converted to Calc Formula Syntax. For example, if the formula in MS Excel on
Windows was originally:
=A2:C2 B1:B3+3
then after import, the Excel A1 formula syntax will (incorrectly) show as:
=A2:C2!B1:B3+3
When Calc is set to Excel A1 Formula Syntax, a '!' in this position should lead
to an error, but instead it shows the result 10, which is therefore a bug.
If you cut the formula and re-paste it into the same cell, it gives a #NAME?
error, which is what you would expect. But it didn't give a #NAME? error
before.
This appears to only be a problem of how the underlying formula is displayed,
not how it calculates (which is why I've categorised the severity as minor).
I suspect what is happening here is that the Excel file format is converted to
Calc A1 formula syntax as an internal representation, but then Calc is failing
to display the underlying Calc syntax in the selected syntax (Excel A1).
Everything said above about Calc's Excel A1 Formula Syntax applies equally to
Calc's Excel R1C1 Formula Syntax.
Everything said above about the intersection operator applies equally to the
union operator (',' in Excel and '~' in Calc). For instance, after import, the
Excel formula
=SUM((A1:A3,B1:B3) A2:D2)
displays in Calc as:
=SUM((A1:A3~B1:B3)!A2:D2)
irrespective of the selected formula synax.
However, I can't think of a use for the union operator.
Whereas, all my business's cash books use the intersection operator
extensively.
This may be related to bug 96426, but I've reported the two separately.
--
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
