Formula intersections can help simplify your formulas. There are 2 types of intersections in Excel: implicit and explicit. In this guide, we will show you implicit vs explicit intersections in Excel.
Implicit Intersections
An implicit intersection where Excel gets a value of a single cell in a range based on the row or column of the formula that contains the reference. For example, in the following image, there are consecutive numbers in A1:A4. Cell C2 contains a basic formula that targets A1:A4. Although the formula refers to the whole range, Excel displays only the 2 values in the same row.
The same principle is also true for the formula in C8. However, this time the formula returns a value based on the column.
You can use this with multiple ranges as well. The following example shows a traditional use case and 2 other ways of using implicit intersections. While the first version uses the reference, the second uses named ranges.
The Total columns are calculated by the values in Qty in Stock and Purchased columns. To apply this to the other rows, copy the formulas or use the AutoFill feature.
On the other hand, you can use the same formulas for the implicit intersection approach. We suggest you use named ranges as well to organize your workbook better. For more information about named ranges, please see Excel Named Ranges.
VALUE Errors
The most likely cause of #VALUE! Errors are the formula placement. You will get this error if the formulas and the referenced ranges don’t share a row or column.
For example, the formula in D28 returns an error because the QtyInStock and Purchased named ranges don’t have a cell in column D or row 28.
Explicit Intersections
Explicit intersections are intersections of two ranges. Unlike implicit intersection, you can supply each range and bind them with an intersection operator - the space character (“ “).
The formula returns the sum of cells in C4:D5 in the following example. The space between B4:E5 and C2:D7 ranges means that Excel will evaluate their intersection, which is C4:D5.
The following example uses the same data. However, this approach copies individual cells from a single table using explicit intersections.
Explicit interceptions do not limit you to share a row or column with the given reference. The only disadvantage is that you need to enter each range individually.
