3.7 Understand implicit intersection - Video Tutorials & Practice Problems

<v ->Implicit intersection is another important concept</v> for data analysts working with Excel. For all Excel products, prior to the dynamic array enabled versions. This behavior was used by Excel to reduce multiple array values to a single value, using the following logic. If the value is a single item, then return the item. If the value is a range, then return the value from the cell on the same row or column as the formula. If the value is an array, then pick the top left value. In these versions of Excel, the implicit intersection logic was executed behind the scenes. As a single cell could only hold one value. The exception here would be Excel tables; which allowed users to reference cells and another column of the same row, using the implicit intersection operator, which is the @ symbol. However, with the latest Excel for Microsoft 365 versions. This operator is used to reference values within a data range that are in the same row or a column as the formula cell. In essence, it allows Excel users to explicitly disable the Dynamic Array behavior and return a single value. In case the resulting values is an array, this operator will return an error. Implicit intersection can also apply to user defined functions. However that is out of scope for this course. Now let's review some examples for this topic. For these examples, we will consider three dynamic arrays. A one dimensional horizontal array with all the column headers, a one dimensional vertical array with all the row headers, and a two dimensional data set. To start with, let's use the implicit intersection operator, to retrieve values in the same column as a formula cell. To do this, we will type in the implicit intersection operator, which is the @ symbol, followed by the define name for the starting cell of the spill range, and then finally the # symbol, for the spill reference notation. This will help us refer to the entire spill range. When we press enter, we can see that it creates the value, in the same column as the formula cell from our column headers spill range. It is important to note, that our result does not change if we use the same formula, on a different row of the same column. (mouse clicks) Now let's use a similar approach to retrieve values from our row headers spill range, in the same row as a formula cell. Once we press enter, we can see that the retrieve value , is from the same row, of our row headers spill range. We can also try to use the same formula, in a different row and column, and expect to see a result, that is from the same row as the new formula cell. Finally, let's try something a little bit more complicated. With this function, we want to retrieve the value, from the second column of our two dimensional data set, which is in the same row as the formula cell. The third argument for our index function, provides the column number. The first argument provides the spill range we wanna refer, which is our two dimensional dataset. And the implicit intersection operator at the start, the @ symbol, tells Excel that we're looking for the value in the same row. The trick here is to use zero as the row number argument for our index function. This in combination with the implicit intersection operator, tells Excel that we're looking for the result from the same row as our formula cell. When we press enter, we can see that the retrieve value, is from the same row as the formula cell, and from the second column in our two dimensional dataset. Okay, so with that, we have now completed our review, of some important topics related to structuring and referencing of data within Excel. Now let's try to incorporate these concepts into a relatively simple data analysis solution.