Loading

May 252012
 

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

This is part 2 of a 2 part post. Continued From Excel Intersection 101: Implicit Intersection

As opposed to using Implicit Intersection, you can also use the intersection operator to force an Intersection between rows and columns. This intersection is called Explicit Intersection. The Intersection operator is a “Space”.

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

This time we have a simple table showing costs for four different products over three years. Below this table under the section titled “Showing Formulas” there is an output table that transposes the totals. This is an example of a nice summary table. It could be located on any sheet in the workbook.

Explicit Intersection Sample

Explicit Intersection Sample

In this summary table, we simply need to get the Total Costs for each of the three years. On the first row of the summary table, we use the traditional way of referencing. On the second row, we create a reference to the Total using Explicit Intersection. On the third row, we use Explicit Intersection with Named Ranges to get the following formula:

=Year2011 Totals    (which simply returns the value in cell E10)

In this example we are forcing Excel to return the value at the intersection of a row and column but it does not need to be in the same row or column as the formula. In fact, it can be on a different sheet entirely. Or even in a different workbook!

The next example takes this concept a stage further. What if we want to refer to a range of cells at the intersection  of several rows and a column. This is illustrated in Row 19. Here we use various methods to sum up the values to get the Total Costs. In C20 we use the traditional method. In D20 we use Explicit Intersection to return the range that we need to sum, and in E20, we use Explicit Intersection combined with Named Ranges to do the same. Note the differences in these three approaches.

Summary

As with Implicit Intersection, there seems to be no performance impact of this approach,. However, using Explicit Intersection offers the same benefits over the traditional approach as Implicit Intersection does but with the addition of these:

  1. Fewer restrictions and even more flexible.
  2. Not limited to being in the same row or column.
  3. Not limited to individual cells. Entire ranges can be referenced.

Explicit 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 entire columns and rows.

If you refer to just a simple range using the traditional approach i.e. with direct cell references, it is very easy to insert rows or columns that will mess up this range. Or just forget that the range is there. Remember, with Best Practices layout, you should always use a column for a particular purpose and rows also should be sectioned  for common purposes. This way, if the rows are grouped and labelled and the column is labelled then it is very hard to mess up this formula. I often come across ranges that users have squeezed in somewhere and forgotten about and then when they modify the spreadsheet, this range gets shifted and formulas derived from it get orphaned.

As Explicit Intersection enables entire ranges to be referenced, it can be used with all the functions that look for multi cell ranges. It allows users to reference areas of the spreadsheet using entire column and entire row references which creates far more robust formulas. It also makes it much easier to work with Named Ranges as just few Named Ranges can be intersected in different ways to create multiple ranges; each one extremely robust and easy to maintain.

Caveats

Explicit Intersection will not work when used in the following situations:

  1. In Array Formulas – Sometimes it’s fine. It depends on the usage.
  2. In Data Validation Formulas.
  3. In Conditional Formatting Formulas.

Having said that, Intersections can be used in all these places as there is a cunning work around. If you are like me and don’t like to compromise your standards and are allergic to direct cell references, then there is always a solution. I will add a link in here when I have written a post on it.

Wrap Up

Personally, I use the Intersection of rows and columns for almost all my references and define Named Ranges for everything. This way my models are extremely robust and are virtually impossible to break. When making key changes, it is very easy simply to redefine a Named Range or do a global replace on a Name. Usually, the time taken to incorporate additional functionality takes only a few minutes before it’s ready to use and the rest of the model is “guaranteed” still to work as expected.

Intersections can be a great way to reference tables (I do not mean Excel Tables which can be references with Structured References)  on other sheets and not worry about what rows and columns are inserted. Actually, its quite similar to using Excel Tables Structured References which are also a good way to work with data in Excel.

I hope this will be enough to get more people to use Intersections as a means of creating references in Excel. I will add a subsequent post on advanced uses of Intersections with more sophisticated examples. However, I have first to submit posts on other areas of Excel that will be used in the more advanced examples.

Downloads Used:
Explicit Intersection Sample Workbook

  5 Responses to “Excel Intersection 101: Explicit Intersection”

  1. Thanks for the tip on intersection techniques. All these years of using Excel and I never knew this functionality was there.

  2. Hi, does anybody know how I would write a formula that would find the value in the intersection the word “Totals” in column B (which is row 10 above) and the range Year2010? In my scenario, it’s possible that the “Total” row is mixed throughout a population and cannot be restricted to one row.

    Thanks for any help.

    • In your scenario, is there only one “Total” in the column?. i.e. it may be on different rows but is the column fixed and is there only one total in the column. If that is the case, you can use the Index, Match combination to locate the row on which the “Total” lies and use this in place of an intersection.

  3. “allergic to direct cell references”

    A joy to read. More often I have seen “names are to be avoided; they may be meaningful to you but are meaningless to your readers and add a level of indirection”. To me, it is having to go to the cell location (which has no relevance to the business logic) that is the indirection.

    Another comment in the spreadsheet standards document was “once you have started naming, where do you stop?” How about, when every direct cell reference has been eliminated?

    • Thanks for your comments Peter. You are spot on with your remarks. The view by many that naming should be avoided because others may not understand it is so counterproductive and hinders progress. If more people used them, more people would learn to understand them!!

 Leave a 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)