Tips for Excel, Word, PowerPoint and Other Applications

OFFSET and Dynamic Ranges

Why It Matters to You

OFFSET is an interesting little formula. To be honest, I've been looking for a reason to use it and found one recently, to sum a year to date value based on a user-entered date. This means that I can dynamically sum any combination of YTD values from January-January through January-December based on the inputted valued. Add a MATCH function into the mix and you can calculate year to date values for any year you choose. Spice it up with INDIRECT and you can determine the same YTD value from a number of different worksheets.

How To

It took me a little bit of time to get my brain around OFFSET and how it's used. Let's take a quick look at the actual functiona and then I'll illustrate how it works. This should be considered a more advanced topic.

OFFSET returns a reference to a range that is a certain number of rows and columns from a given reference. One thing that is important to remember about OFFSET is that it returns a range of values, and not a range reference, which is an important distinction. OFFSET requires 5 inputs:

  1. Reference. A single cell reference point upon which the other 4 adjustments will be determined. The upper left hand corner of your array will be determined by the reference point plus a Row and Column adjustment.
  2. Rows. The number of rows to shift from the Reference. A value of 1 means 1 row down from the reference, a value of -1 means 1 row UP from the reference and a value of 0 would indicate no shift in rows.
  3. Columns. The number of columns to shift from the Reference. A value of 1 means 1 column to the right of the reference, a value of -1 means 1 column to the left of the reference and a value of 0 would indicate no shift in columns.
  4. Height. The number of rows to include in your array.
  5. Width. The number of columns to include in your array.

Still a little confusing? Maybe the following two examples will help clarify things. Here's are two arrays that I've described with an OFFSET function. Both use cell A9 as the Reference. The first example uses a Row and Column adjustment of 0, meaning that I haven't shifted my reference point. The Height is 8 and the Width is 1, meaning that I want the 8 cells from D9:D16, which show Actual Spend from January 2008 - August 2008. The second example is almost the same, but with an important difference. The Height (8) and Width (1) of the array are still the same, but I've shifted my Column value by 1 to pick up the 2009 values instead of the 2008 values. Hmmmmm.

So the next logical question is, why don't you just specify the array instead of going through all of this trouble with OFFSET. The key here is that all 5 arguments in the OFFSET function can be dynamic. Instead of hardcoding a range of cells e.g., D9:D16, I can use a number of functions (MONTH, YEAR, MATCH, CHOOSE) or input methods (Data Validation, Forms Tools, etc) to dynamically describe a range of cells, on the fly.

Here's the data table set up that I'm going to use for the example. I have an input box of the date and based on the date, I want to pull in a YTD spend value based on the values in the Actual Spend table. This means that the range of cells summed is going to depend on what year and month is inputted as the date.

You can also download, offset.xls to play around with this on your own.

Here's what the formula for calculating the YTD value looks like. Yikes!

Let's break it down argument by argument. First off, we start out with a SUM function, since that is what we want to do with the results of OFFSET. Since OFFSET returns a range of values, it can't really stand by itself. The values have to be used by another function:

  1. Reference (A9). I want to anchor my OFFSET on the first month of the first year.
  2. Rows (0). Regardless of the date, I always want to start off with January.
  3. Columns (MATCH(YEAR($D$4),D8:E8,0)-1). I want to shift my Column reference either 0 columns for 2008 or 1 column for 2009. I can use a YEAR function on the date and MATCH it against the column headers to derive a value of 1 or 2. I need to adjust this by -1 so that my column values are either 0 or 1.
  4. Height (MONTH($D$4)). MONTH returns a value of 1-12, depending on the month. This works perfectly for my OFFSET since the value is exactly the number of rows I want to include in my array.
  5. Width (1). I'll either want 2008 OR 2009, but never both. Hence, the width of my array will be fixed at 1 column wide.

OFFSET isn't the easiest function to grasp immediately, but if you play around with it a bit and experiment, I think you'll find that is it both very flexible and useful.

Notes

Last updated8/27/08
Application VersionExcel 2003
AuthorMichael Kan
Pre-requisitesNone
Related Tips INDEX-MATCH Combos
Introduction to INDIRECT