r/excel•
GregHullender

Best practice for the @ operator

Discussion

Microsoft's documentation for the implicit intersection operator, aka the at sign, is rather baffling for something so simple: @ array simply returns the first element in the array. As a scalar value, not a one-element array.

Consider this example:

=LET(col, {"2";"A";"B"},
  n, CHOOSEROWS(col,1),
  SEQUENCE(n)
)

It selects the first element from col and creates a sequence of that length. The answer should be a column of 1 and 2. But it only generates 1. This is because CHOOSEROWS(col,1) has created a one-element array--not a scalar value. TAKE and INDEX have the exact same problem.

You can do crazy things to turn this into a scalar, e.g. SUM(--CHOOSEROWS(col,1)) works, but it's much easier to just put an @ in front. \@CHOOSEROWS gives the desired result.

However, once you know @ just selects the first element, why not just use

=LET(col, {"2";"A";"B"}, SEQUENCE(@col))

It's clean and it's simple--provided everyone understands what it does. But is that a fair assumption?

Posted May 4, 2025, 4:33 PM UTC

125
Loading comments…