The implicit intersection operator was introduced as part of substantial upgrade to Excel's formula language to support dynamic arrays. Dynamic arrays bring significant new calculation ability and functionality to Excel.
Upgraded Formula Language
Excel's upgraded formula language is almost identical to the old language, except that it uses the @ operator to indicate where implicit intersection could occur, whereas the old language did this silently. As a result, you may notice @'s appear in some formulas when opened in dynamic array Excel. It's important to note that your formulas will continue to calculate the same way they always have.
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 @.
Why the @ symbol?
The @ symbol is already used in table references to indicate implicit intersection. Consider the following formula in a table =[@Column1]. Here the @ indicates that the formula should use implicit intersection to retrieve the value on the same row from [Column1].
Can you remove the @?
Often you can. It depends on what the part of the formula to the right of the @ returns:
-
If it returns a single value (the most common case), there will be no change by removing the @.
-
If it returns a range or array, removing the @ will cause it to spill to the neighboring cells.
If you remove an automatically added @ and later open the workbook in an older version of Excel, it will appear as a legacy array formula (wrapped with braces {}), this is done to ensure the older version will not trigger implicit intersection.
When do we add the @ to old formulas?
Generally speaking, functions that return multi-cell ranges or arrays will be prefixed with @ if they were authored in an older version of Excel. It's important to note that there is no change to the way your formula behaves — you can just see the previously invisible implicit intersection now. Common functions that could return multi-cell ranges include INDEX, OFFSET, and User Defined Functions (UDFs). A common exception is if they are wrapped in a function that accepts an array or range (e.g. SUM() or AVERAGE()).
See Excel functions that return ranges or arrays for more details.
Examples
| Original formula | As seen in dynamic array Excel | Explanation |
|---|---|---|
| =SUM(A1:A10) | =SUM(A1:A10) | No change - No implicit intersection could occur, as the SUM function expects ranges or arrays. |
| =A1+A2 | =A1+A2 | No change - No implicit intersection could occur. |
| =A1:A10 | =@A1:A10 | Implicit intersection will occur, and Excel will return the value associated with the row the formula is in. |
| =INDEX(A1:A10,B1) | =@INDEX(A1:A10,B1) | Implicit intersection could occur. The INDEX function can return an array or range when its second or third argument is 0. |
| =OFFSET(A1:A2,1,1) | =@OFFSET(A1:A2,1,1) | Implicit intersection could occur. The OFFSET function can return a multi-cell range. When it does, implicit intersection would be triggered. |
| =MYUDF() | =@MYUDF() | Implicit intersection could occur. User Defined Functions can return arrays. When they do, the original formula would have triggered implicit intersection. |
Using the @ operator in new formulas
If you author or edit a formula in dynamic array Excel that contains the @ operator, it may appear as _xlfn.SINGLE() in pre-dynamic array Excel.
This occurs when you commit a mixed formula. A mixed formula is a formula that relies on both array calculation and implicit intersection, this was not supported by pre-dynamic array Excel. Pre-dynamic array only supported formulas that did i) implicit intersection or ii) array calculation throughout.
When dynamic array enabled Excel detects the creation of a "mixed formula", it will propose a variation of the formula that implicit intersection throughout. For instance, if you enter =A1:A10+@A1:A10, you will see the following dialog:
If you chose to reject the formula proposed by the dialog, the mixed formula =A1:A10+@A1:A10 will be committed. If you later open this formula in pre-dynamic array Excel, it will appear as =A1:A10+_xlfn.SINGLE(A1:A10) with the @'s in the mixed formula appearing as _xlfn.SINGLE(). When this formula is evaluated by pre-dynamic array Excel it will return a #NAME! error value.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in the Answers community.
No comments:
Post a Comment