Loading

May 242012
 

This post applies to the following versions of Excel: Excel 2003, Excel 2007 & Excel 2010

Excel has a little known but very powerful feature that can help users to build much simpler and easier to read formulas and, if used correctly, can also help to make models much easier to modify without breaking  references or referencing the wrong cells. The key to this is Implicit Intersection.

First we will look as some simple ways to use it. Here is a screenshot of the sample book which can be downloaded here.

Implicit Intersection Sample

Implicit Intersection Sample

We have a simple sheet with three columns of numbers (C,D,E: column headings coloured in blue). The first column is a list of quantities (Qty), the second is a list of prices (Price). We want to know the cost for each product.

In Column E you can see the traditional way of setting up this calculation.

=C5*D5

Now look at column F. We have repeated the setup to the right to make it easy to compare. This time we have referenced both entire columns.

=C:C*D:D

But the result is the same. This is because Excel will automatically return the value in the referenced column by intersecting the column and the current row.

With the sample workbook open, move the mouse down the cells in Column E. You will see every cell has a different formula. The formulas are consistent but if there was an error, would you easily spot it?  Now do the same exercise on Column F. The formulas are identical. You can immediately notice when one is wrong.

Does this impact performance? No. I have run tests using thousands of rows and the calculation speed is identical. So the initial benefit here is really in making formulas much easier to read and making it easier to spot errors.

Now, if you are to add range names to this, you will see even more, how useful this can be. Named Ranges are covered in detail here. This time, we have the same concept but we have named both the Qty and Price columns (The headings of the Named Ranges are coloured in orange). Now compare the two formulas and decide which is easier to use. Look at Column G.

= Price x Qty

There are many easy ways to trace what range a Named Range refers to. Again this will be covered in another post.

Implicit Intersection works in the same way with rows as it does with columns. Excel will intersect the current column. This is shown in Row 12 in the sample workbook. This row simply gets the data from Row 10 but it can be used in more complex formulas.

Summary

Using Implicit Intersection does the following:

  1. Makes formulas easier to read.
  2. Makes errors easier to spot.
  3. Reduces the chance of making errors.  It is quite easy to click on the wrong cell when editing a formula. It is far easier to click on a column heading.
  4. Makes formula more flexible to modify in the future.

Implicit Intersection will also work on ranges that are not entire columns or rows. However, in most cases it is easier and more reliable to work with full columns and rows.

Caveats

Implicit Intersection will not work in an array formula as array formulas are designed to work on each element in the given range. It will however work when referencing the result of an array formula. Array formulas will be covered in detail at a later date but in the meantime if you need further information, google Excel Array Formula.

This is part 1 of a 2 part post. Continued in Excel Intersection 101: Explicit Intersection

Downloads Used:
Implicit Intersection Sample Workbook

  6 Responses to “Excel Intersection 101: Implicit Intersection”

  1. This brings tears of joy to my eyes. Thank you. This makes things so much easier.

  2. […] document.write(''); Name cell D4 as a named range, say DFour Formula: =AVERAGE(DFour:Score1) ($D:$D:D13) is not a valid reference, pretty sure no one considers that a bug. As a test: Change your Score name reference to D4:D13 instead of the whole D column. In cell E14 type the formula =sqrt(score), do you get #VALUE? Type =sum(score) do you get #VALUE, no, why not? Implicit intersections. […]

  3. Hi, how did you manage to show formula syntax in Excel test file? What it the secret? special formatting as text and something else? Please share. Thank you for your article.

  4. Congratulations!
    Fantastic walk through of the Intersection tool with pros and cons and performance issues.
    One of the clearest and most clarifying presentations I have seen.
    You made my day!
    🙂

  5. I was on one the excel forums asking for guidance with a problem I was having, I have a non-table sheet with many named-columns and a number of rows. It seems that I had been using implicit intersection without knowing what it was called. I am glad that I know it now b/c when I researched what I was trying to do, google came up empty.

    In my worksheet, I found that I could use operators easily: “+”, “-“, “*” and “/” would give the result I wanted referencing the cells in the result’s row.

    However, using some standard functions did not give me the results I had hoped for. I tried sum and max, for example, for column1, column2 & column3. Sum gave a result of the sum of all the rows for column1, column2 & column3 when I only wanted it for the row that the result was on.

    I wonder if there is something similar to the “@” for non-tables that would let me reference on “this” row.

    • Functions like sum and avg are array functions and will not work with intersection. If you wish to use the name in the sum function you will need to refer to the specific cell by reference. The way I do this is to use the index and column or row function. i.e. AA_Totals becomes index(AA_Totals,column()) in your sum function. That way it will refer always to the column that the formula is in and not sum the entire row. If summing columns you would use Row()

Leave a Reply to Inna Kravchenko Cancel reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)