Exceljet

Quick, clean, and to the point

Implicit Intersection

Excel implicit intersection example

Implicit Intersection describes a behavior in Excel that occurs when a range is passed to a function that expects a scalar (single) value. If this situation, Excel will try to resolve the formula using a reference in the same row, or in the same column. 

Implicit intersection can occur when a formula is entered next to vertical data, or above or below horizontal data. For example, the formula in D6 in the example shown is:

=B4:B8+1

In this case, Excel resolves the range B4:B8 to the value in B6 (3) and returns a result of 4. When Excel can't determine a single reference in a formula where a range is passed, but a single value is expected, a #VALUE error is returned. For example, if same formula above is entered in cell D9, the result is #VALUE.

CSE Array formulas

Entering an array formula with control + shift + enter (CSE) explicitly disables the implicit intersection behavior. This makes it possible to create formulas that manipulate multiple values input as ranges. If the formula in D6 is wrapped in SUM, then entered with control + shift + enter:

=SUM(B4:B8+1)

All values in the range are processed, and the formula returns 27.

Excel Tables

Implicit intersections can be useful in Excel Tables, where the same formula can be used in multiple cells (for consistency) but continue to resolve to a single cell at the row label. For example, this formula would add 7 days to the value in a "date" column in table:

=table[date]+7

Even though the formula refers to the entire "date" column, the formula will operate on a single value in the date column at the row level.

Problems 

Implicit intersections can sometimes cause problems because they can return results that look normal but are actually incorrect. For example, if an array formula is entered next to vertical or horizontal data, and a user forgets to enter with Control + Shift + Enter, the formula may return an incorrect result.