Just recently the @ sign has been appearing in my functions in Excel 365 (or I've only just noticed it).

I have looked up why this is and - confession time - I am stuggling to understand the explanations about "implicit intersections"

The odd thing is that the appearance of the @ seems to be quite random and my functions never seem to need it.

I would be most grateful if someone can explain this in non-jargon language please.



Thanks

Only '@' or something like '[@YourColumnHeader]' or '[@[Your Column Header]]'?

When more than '@', then you probably defined your regular Excel table as a Defined Table, leaving formulas there not converted into format used by Defined Tables. And when you now edit some formulas, or add new rows there, the Defined Tables formula syntax is applied for updated/added cells.

When you create a Defined Table, you determine for it a name (e.g. tMyTable]. Column headers in Defined Table will be names of data columns. NB! It will be a good practice having no spaces in column headers, as otherwise you need an additional pair of square brackets in every reference to values in this column.

Now, to refer the whole datarange in some column in same Table you have designing the formula, you do this like:
[YourColumnName] or [[Your Column Name]];
To refer to whole datarange in some column in some other Defined Table anywhere in your workbook, you do this like:
NameOfOtherTable[NameOfColumnInOtherTable];
To refer to cell in same row in some column in same Table you have designing the formula, you do this like:
[@YourColumnName]
(NB! You can refer in this way to cell with same row number in any other Table too, by simply perceeding the reference with source Table name, but this assumes, this other Table must be always aligned with one you enter the formula into - practically you never can guarantee this!);
To refer to whole datarange of any Defined Table in your workbook, you do this like:
NameOfTable;
To refer to certain header cell in any Defined Table, you do this like:
YourTableName[[#Headers];[YourColumnName]]
To refer to whole header range of any Defined Table, you do this like:
YourTableName[#Headers]

To refer to cell in another row as the one you are entering the formula in, there isn't any simple way. You either refer to it using regular syntax, or you do this like INDEX([YourColumnName],RowNumber). The row number you enter either manually, or you can have a column in your Table, where all Table rows are numbered by formula automatically, and you use SUMIFS() to calculate the row number which must be used.

NB! Whenever you change the name of any Defined Table on workbook, or name of column in any of Defined Tables there, all formulas in this workbook using Named Table syntax will be updated immediately with this change!

Thank you very much - I get that.

The odd thing is that none of my Workbooks have tables !

I guess I can just ignore the @ signs then.

Right! The @ signs that you see have nothing to do with Excel tables.

And in fact, calling it an "implicit" intersection operator is a misnomer, since obviously it is an explicit operator in recent versions of Excel.

Moreover, you cannot "just ignore" the @ signs.

-----

Historically, an "implicit" intersection might occur when we normally-enter (just press Enter) an expression like IF($A$1:$A$100=10, 123, 456) into any of rows 1 through 100.

Excel would "implicitly" interpret that as IF(A5=10, 123, 456) in row 5, for example.

And Excel would return a #VALUE error if expression were entered in any other row.

Moreover, that is why we had to array-enter expressions (press ctrl+shift+Enter) in some contexts. For example, PRODUCT(1+$A$1:$A$100) where our intent is (1+A1)*(1+A2)*...*(1+A100) instead of simply PRODUCT(1+A5) in row 5.

-----

Fast-forward to recent versions of Excel that support so-called "dynamic array awareness" starting with Excel 2016. (Another misnomer, since there is nothing necessarily "dynamic" about it.)

In such versions, SUM(IF($A$1:$A$100=10,123,456)) is "implicitly" interpreted as an array reference meaning IF(A1=10,123,456)+IF(A2=10,123,456)+...+IF(A100=10 ,123,456) by default.

So, if our intent were SUM(IF(A5=10,123,456)) in row 5, ostensibly now we must explicitly write @SUM(IF($A$1:$A$100=10,123,456)).

I write "ostensibly" because, in my limited experience, I'm not sure that __we__ can enter the @ operator explicitly to indicate "intersection" (single-valued result).

IOW, MSFT flipped(!) the "implicit" interpretation of an expression without the @ sign.

And yes, that is based on Excel's "structured reference" (table) syntax, which existed long before "dynamic array awareness" and the "spill" feature.

Hope that helps sort this out.