==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)
Reproduced. Win 7 Pro 64-bit Version: 5.2.0.0.alpha0+ Build ID: 014633f83e44ae8ba33087b6f38e8e253e281969 CPU Threads: 4; OS Version: Windows 6.1; UI Render: default; TinderBox: Win-x86@62-merge-TDF, Branch:MASTER, Time: 2015-12-15_06:21:44 Locale: fi-FI (fi_FI)
Oh yeah, conditionally significant whitespace as operator, great fun.. also in OOXML. One has to bow before the wisdom of Excel devs.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=26adceb098134d918f6d57c8687ab057e24adc39 Resolves: tdf#96426 significant whitespace as intersection in Excel syntax It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=b0992e11905e36a64edeb92a13acfde5837c1878 fully check for adjacent RPN end, tdf#96426 follow-up It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=e0875f8e348a3aca036bc0cc629fb038fabc8062 narrow down where a space could be an intersection, tdf#96426 follow-up It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Also https://cgit.freedesktop.org/libreoffice/core/commit/?id=0f8a8332a52cd03b43aaab86e0c232e0964d7111
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=0c5663cfb13f4f55e246d42ac464d5e2c2f23099 first range can be anywhere before second at RPN end, tdf#96426 follow-up It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review https://gerrit.libreoffice.org/24374 for 5-1
(In reply to Eike Rathke from comment #8) > Pending review https://gerrit.libreoffice.org/24374 for 5-1 Thx for the fix. I'll test the daily build as soon as I can, using our cashbook spreadsheets, which liberally use the intersection operator in Excel formula syntax. I'll also try to contrive some more challenging tests. However, I have bigger problems at the mo, which I need to fix first (crash while not even touching my machine left me with black-screen on boot). So prob a fortnight. I'm afraid I cannot review the code. I've had a quick look at your diffs, but I'm completely unfamiliar with Libre Office code, so I won't promise to review it - would be pointless.
(In reply to Bob Briscoe from comment #9) > I'm afraid I cannot review the code. I've had a quick look at your diffs, > but I'm completely unfamiliar with Libre Office code, so I won't promise to > review it - would be pointless. It's ok - we are not expecting you to review it :) If you are interested in contributing without coding, see https://wiki.documentfoundation.org/QA/Triage_For_Beginners for an intro to the vast world of quality assurance.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=edd4370f5ba49a26a526995b6a28f623d68041ce check presence of token, tdf#96426 follow-up It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=b8d78268d1c58557122f99d6134a3b1eda7fe6c3&h=libreoffice-5-1 Resolves: tdf#96426 significant whitespace as intersection in Excel syntax It will be available in 5.1.4. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
THank you for trying to fix this, but it's not completely fixed, I'm afraid. The fix works with intersections between simple cell ranges, but you get the two errors described in the original bug report when the intersection is defined between variable names (defined as cell ranges using Insert>Names>Define). See the screenshots at http://homefarmparham.co.uk/tmp/calc/IntersectCalcBugReopen.html in which the red text has been added since the original bug report. For instance, if you define: * horiz as Sheet1!$2:$2 * vert as Sheet1!$B:$B Then, with Excel A1 formula syntax, the following example formulae return the errors shown: =(vert horiz)*2 Err:508 (pair missing) =vert horiz Err:509 (missing operator) _______ I just noticed an extra twist: If an intersection formula is written with a '!' when Calc A1 formula syntax is selected, e.g.: =(vert!horiz)*2 a) this works correctly with no errors. b) the '!' correctly auto-translates to a ' ' (space) if the formula syntax is switched to Excel A1. c) And there are still no errors, but... d) ...if the formula is edited to a different formula with ' ' (space) as an intersection operator, the bug re-appears.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=70cb7757f971c4c16d1b0a5585d95816cd4382e6 Resolves: tdf#96426 more whitespace intersection operator in Excel syntax It will be available in 5.4.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review https://gerrit.libreoffice.org/37712 for 5-3
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=67a4bb2204c841bf468504fcf5282787abc0e973 add unit test for Excel whitespace operator intersection, tdf#96426 It will be available in 5.4.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=8326c48a1ac4855970f9232dba582a51461ac0b2 Unit test to ensure that calculating with the intersection works, tdf#96426 It will be available in 5.4.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-3": http://cgit.freedesktop.org/libreoffice/core/commit/?id=9c89d41ed44c1a4126b82f0c05768f5d640f741b&h=libreoffice-5-3 Resolves: tdf#96426 more whitespace intersection operator in Excel syntax It will be available in 5.3.4. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.