Search code examples
excelnumbersrangeformulacell

What's the meaning of "cell range as a formula" in MS Excel / Apple Numbers

Step to reproduce:

  1. Create an Excel file or Numbers file.

  2. Fill cells from A1 to A10 with 1~10 (An has value n).

  3. Assign cells from B1 to B11 with formula $A$1:$A$10 (WITHOUT SUM function).

    And we will get this. enter image description here enter image description here

  4. The same formula $A$1:$A$10 gets different values from B1 to B11. Bn has same value as An when 1 <= n <=10, and B11 got error value.

  5. The same thing happens with formula of single row or column. But if formula cell range has more than one rows/columns. All cell with formula gets error value.

  6. The same logic happens in both Excels & Numbers, And other similar softwares.

So I want to make sure that, is this a feature or just a bug? Is there some documentation related to this?

Solution

  • It is a feature called "Implicit Intersection". Effectively the formula says: Get the value in A1:A10 that is in the same row as this formulae. That works if the formula is in 1 to 10, but fails for row 11 because there is no row 11 in the range A1:A10.

    Note that your formula doesn't work as described in Excel 365. Instead, you'd need to use the new implicit intersection operator, like @$A$1:$A$10 see https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34