Description: What is implicit intersection? Implicit intersection logic reduces many values to a single value. Excel did this to force a formula to return a single value, since a cell could only contain a single value. If your formula was returning a single value, then implicit intersection did nothing (even though it was technically being done in the background). The logic works as follows: * If the value is a single item, then return the item. * If the value is a range, then return the value from the cell on the same row or column as the formula. * If the value is an array, then pick the top-left value. With the advent of dynamic arrays, Excel is no longer limited to returning single values from formulas, so silent implicit intersection is no longer necessary. Where an old formula could invisibly trigger implicit intersection, dynamic array enabled Excel shows where it would have occurred with the @. Steps to Reproduce: 1. In cell C1 enter: 10 2. In cell C2 enter: 20 3. In cell C3 enter: 30 4. In cell C4 enter: 40 5. In cell A2, enter: =@C:C Actual Results: Err:509 Expected Results: 20 Reproducible: Always User Profile Reset: No Additional Info: https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34 https://bettersolutions.com/excel/formulas/implicit-intersection-operator.htm https://benf.org/excel/spill_performance/ SINGLE Function (Removed in 365) This function was only provided in Excel 2021 for backwards compatibility and was removed in Excel 365. SINGLE - Returns the value from a cell range which is the intersection of a row OR a column. If you try and use this function you will see a warning pop up message and the function will be automatically replaced with the "@" character. You will no longer get implicit intersection of your formulas, instead, where it detects an implicit intersection, the "@" character or this function will be added.
+1
@Óvári: can you please provide a sample XLSX created in MSO that illustrates the use of the operator? @Eike: what do you think?
(In reply to Stéphane Guillou (stragu) from comment #2) > @Óvári: can you please provide a sample XLSX created in MSO that illustrates > the use of the operator? https://www.ablebits.com/office-addins-blog/excel-implicit-intersection/ Is this link sufficient? Thank you
Created attachment 194926 [details] sample XLSX (In reply to Óvári from comment #3) > (In reply to Stéphane Guillou (stragu) from comment #2) > > @Óvári: can you please provide a sample XLSX created in MSO that illustrates > > the use of the operator? > https://www.ablebits.com/office-addins-blog/excel-implicit-intersection/ > Is this link sufficient? It helps understanding the feature, yes, but it's always useful to have a sample document to see it in action straight away, and to see how the import currently behaves in LO. Here's a simple XLSX based on what you linked.
(In reply to Stéphane Guillou (stragu) from comment #2) > @Eike: what do you think? Well, what shall I say, yet another Excel feature that might get implemented some day. However, note that this @ operator seems to be a UI-only thing only necessary because Excel changed its default behaviour when entering such formulas to not result in an implicit intersection but spilled result. Stored in file here is D10: B10:B13*C10:C13 F18: INDEX(B18:C21,,F17) and these work identical without the @ operator, there is an implicit intersection created for operators or function parameters that do not force a cell range to an array argument. From the attached example there is no benefit because Calc still uses the behaviour that Excel changed.
(In reply to Eike Rathke from comment #5) > From the attached example there is no benefit because Calc still uses the > behaviour that Excel changed. The implicit operator is part of the whole new "dynamic arrays" features (Excel 365 – a moving target with changing rules – and Excel 2021 ATM), not yet supported by Calc. This is a different syntax (at least from the POV of users), and there are new behaviors too, such as automatically adapting ranges (both for input and for output arrays), not using CSE for dynamic arrays, new error codes/messages (#spill is only one of them) and more.
Currently LibreOffice has no support for "dynamic array" but changes the functions to CSE on import from Excel. LibreOffice needs to decide how to treat documents that currently use implicit intersection, when the feature "dynamic array" will be implemented. Thus this request depends on bug 127808.
Intersection of a Single Row and Column =C2:C13 B5:D5 Intersection of a Multiple Rows and Columns =B2:C13 B5:D5 Intersection of Named Ranges =Prdt1 Apr =Prdt1:Prdt2 Apr Note: This invisible intersect operator get precedence over other operators. So if in this case, if you use =INDIRECT(B16) INDIRECT(C15)>5000, it will return TRUE or FALSE based on the intersecting value. https://trumpexcel.com/intersect-operator-in-excel/
(In reply to Óvári from comment #8) > Intersection of a Single Row and Column Please, let's not mix things. The new implicit intersection is part of the new dynamic arrays concept in Excel 2021 (and 365). The intersect operator (a space character in Excel, the exclamation "!" character in Calc) is not new, and it is already supported in Calc. When introducing dynamic arrays to Calc, there might (or might not) be something to do with the old intersect operator (just as there might or might not be things to do with every other operator), but it is not directly related to the concept of implicit intersection of dynamic arrays. These are two different concepts.
(In reply to ady from comment #9) > The intersect operator (a space character in Excel, the exclamation "!" > character in Calc) is not new, and it is already supported in Calc. Thank you, did not know that "!" and " " are intersection operators in Calc and Excel, respectively. When typed " " in Calc got an error, it did not update to an "!"
Seems there's no opposition to it being implemented for ease of transition between office suites, so setting to "new". Thanks for clarifying it's a UI convenience thing that doesn't persist in the file, Eike.
Created attachment 194963 [details] Sample Excel LTSC 2021 file =FORMULATEXT() from Excel converts to FORMULA() in Calc (hopefully it converts back if required LibreOffice seems to silently remove the `@` character. Excel LTSC 2021 and LibreOffice Calc screenshots to compare the differences will follow.
Created attachment 194964 [details] Sheet1 screenshot based on Excel LTSC 2021 file
Created attachment 194965 [details] Sheet2 screenshot based on Excel LTSC 2021 file
Created attachment 194966 [details] Sheet3 screenshot based on Excel LTSC 2021 file
(In reply to Óvári from comment #12) > =FORMULATEXT() from Excel converts to FORMULA() in Calc (hopefully it > converts back if required Yes it does (saved as _xlfn.FORMULATEXT(...)). You could easily try by saving the loaded document to a new .xlsx file and either inspect its (zip) xl/worksheets/sheet*.xml streams, or loading it in Excel. > LibreOffice seems to silently remove the `@` character. As I mentioned in comment 5 there is no @ character stored in the formula thus Calc does not remove it. If it was present in the formula then the result would be a #NAME? error or similar. The cell saved in the .xlsx xl/worksheets/sheet3.xml stream is literally <c r="B3" s="14"> <f>VLOOKUP(A:A,D:E,2,FALSE)</f> <v>15</v> </c> It's all an Excel UI feature depending on whether it has dynamic arrays enabled or not. See also https://bugs.documentfoundation.org/show_bug.cgi?id=127808#c2 of bug 127808 that Regina pointed to in comment 7.