Formula intersections can help simplify your formulas. There are 2 types of intersections in Excel: implicit and explicit. In this guide, we’re going to show you implicit vs explicit intersections in Excel.
Implicit Intersections
An implicit intersection what 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. The cell C2 contains a basic formula which targets A1:A4. Although, the formula refers to the whole range, Excel displays only the 2 value, which is 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 can 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 is the formula placement. If the formulas and the referenced ranges don’t have share a row or column, you will get this error.
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. As opposed to 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.
