Chapter 20: The OFFSET Function | MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)

Overview

How can I create a reference to a range of cells that is a specified number of rows and columns from a cell or another range of cells?
How can I perform a lookup operation based on the right-most column in a table range instead of the left-most column?
I often download software product sales information listed by country. I need to track revenues from Iran as well as costs and units sold, but the data about Iran isn’t always in the same location in the worksheet. Can I create a formula that will always pick out Iran’s revenues, costs, and units sold?
Each drug developed by my company goes through three stages of development. I have a list of the cost by month for each drug, and I also know the length in months of each development stage. Can I create formulas that compute for each drug the total cost incurred during each stage of development?
I run a small video store. In a worksheet, my accountant has listed the name of each movie and the number of copies in stock. Unfortunately, he combined this information in one cell for each movie. How can I extract the number of copies of each movie in stock to a separate cell?
How can I write a formula that always returns the last number in a column?
How can I set up a range name that automatically includes new data?
I am charting my company’s monthly unit sales. Each month, I download the most recent month’s unit sales. I would like my chart to update automatically. Is there an easy way to accomplish this?

The OFFSET function is used to create a reference to a range that is a specified number of rows and columns away from a cell or range of cells. Basically, to create a reference to a range of cells, you first specify a reference cell. You then indicate the number of rows and columns away from the reference cell that you want to create your range. For example, by using the OFFSET function, I can create a reference to a cell range that contains two rows and three columns and begins two columns to the right and one row above the current cell. You can calculate the specified number of rows and columns you move from a reference cell by using other Excel functions.

The syntax of the OFFSET function is OFFSET(reference,rows moved,columns moved, height,width) where:

Reference is a cell or range of cells from which the offset begins. If you specify a range of cells, the cells must be adjacent to each other.
Rows moved is the number of rows away from the reference cell or range that you want the range reference to start (the upper-left cell in the offset range). A negative number of rows moves you up from the reference; a positive number of rows moves you down. For example, if reference equals C5 and rows moved equals –1, you move to row 4. If rows moved equals +1, you move to row 6. If rows moved equals 0, you stay at row 5.
Columns moved is the number of columns away from the reference cell or range that you want the range reference to start. A negative number of columns moves you left from the reference; a positive number of columns moves you right. For example, if reference equals C5 and columns moved equals –1, you move to column B. If columns moved equals +1, you move to column D. If columns moved equals 0, you stay at column C.
Height and Width are optional arguments that give the number of rows and columns in the offset range. If height or width is omitted, the OFFSET function creates a range for which the value of height or width equals the height or width of the reference cell or range.

How can I create a reference to a range of cells that is a specified number of rows and columns from a cell or another range of cells?
The file Offsetexample.xlsx, shown in Figure 20-1, provides some examples of the OFFSET function in action.

Figure 20-1: Using the OFFSET function
For example, in cell B10, I entered the formula (shown in cell A10) SUM(OFFSET(B7,– 1,1,2,1)). This formula begins in cell B7. We move one row up and one column to the right, which brings us to cell C6. We now select a range consisting of two rows and one column, which yields the range C6:C7. The SUM function adds the numbers in this range, which yields 2+6=8. The other two examples shown in Figure 20-1 work the same way. In the following sections, we’ll apply the OFFSET function to solve some problems that were sent to me by former students working at major U.S. companies.
How can I perform a lookup operation based on the right-most column in a table range instead of the left-most column?
In Figure 20-2 (see workbook Lefthandlookup.xlsx), I’ve listed the members of the Dallas Mavericks NBA basketball team and their field goal percentages. If I’m asked to find the player with a specific field goal percentage, I could easily solve that problem by using a VLOOKUP function. But what I really want to do is a “left-hand lookup,” which involves finding the field goal percentage for a player by using his name. A VLOOKUP function can’t perform a left-hand lookup, but a left-hand lookup is simple if you combine the MATCH and OFFSET functions.

Figure 20-2: You can do a left-hand lookup by using the MATCH and OFFSET functions.
First, I enter the player’s name in cell D7. Then I use a reference cell of B7 (the field goal percentage column header) in the OFFSET function. To find the player’s field goal percentage, we need to move down to the row below row 7 where the player’s name appears. This is a job for the MATCH function. The MATCH function portion of the formula OFFSET(B7,MATCH(D7,$C$8:$C$22,0),0) moves down to the row containing the specified player’s name and then moves over 0 columns. Because the reference consists of one cell, omitting the height and width arguments of the OFFSET function ensures that the range returned by this formula is also one cell. Thus, we pick up the player’s field goal percentage.
I often download software product sales information listed by country. I need to track revenues from Iran as well as costs and units sold, but the data about Iran isn’t always in the same location in the worksheet. Can I create a formula that will always pick out Iran’s revenues, costs, and units sold?
The file Asiansales.xlsx (see Figure 20-3) contains data for the units sold, sales revenue, and variable cost for software sold to several countries in Asia and the Middle East. Each month, when we download the monthly financial reports, the location of each country in the worksheet changes, so we want formulas that will always return (for a given country) the correct units sold, revenue, and variable cost.

Figure 20-3: You can use the OFFSET function in calculations when you’re working with data that isn’t always in the same location in a worksheet.
By copying from D21 to E21:F21 the formula OFFSET($C$6,MATCH ($C21,$C$7:$C$17, 0),D20), we compute the result we want. This formula sets reference equal to cell C6 (which contains the word Country). Then it moves over one column (to cell D20) to find units sold and down to the row containing the country listed in C21. In cell E21, the reference to D20 now refers to E20 and becomes a 2, so we move over two columns to the right of column C to find revenue. In cell E21, the reference to D20 now refers to F20 and becomes a 3, so we move three columns to the right of column C to find variable cost.
Each drug developed by my company goes through three stages of development. I have a list of the cost by month for each drug, and I also know how many months each development stage took for each drug. Can I create formulas that compute for each drug the total cost incurred during each stage of development?
The file Offsetcost.xlsx contains the monthly costs incurred to develop five drugs and, for each drug, the number of months required to complete each phase. A subset of the data is shown in Figure 20-4.

Figure 20-4 Using the OFFSET function to compute development costs for Phases 1–3.
The goal is to determine for each drug the total cost incurred during each development phase. In cells D4:D6, I compute the total development costs for Phases 1–3 for Drug 1. I compute Phase 1 costs for Drug 1 by using a cell reference of D10, with rows moved and columns moved equal to 0. Setting height equal to the number of months in Phase 1 and width equal to 1 captures all Phase 1 costs. I compute Phase 1 costs for Drug 1 in cell D4 with the formula SUM(OFFSET(D10,0,0,D1,1)). Next, in cell D5, I compute Phase 2 total costs for Drug 1 by using the formula SUM(OFFSET(D10,D1,0,D2,1)). Note that I start with a cell reference of D10 (the first month of costs) and move down the number of rows equal to the length of Phase 1. This brings me to the beginning of Phase 2. Setting height equal to the value in cell D2 ensures that we include all Phase 2 costs. Finally, in cell D6, I find the Phase 3 development costs for Drug 1 by using the formula SUM(OFFSET(D10,D1+D2,0,D3,1)). In this formula, I start from the first month of sales and move down the number of rows equal to the total time needed for Phases 1 and 2. This brings us to the beginning of Phase 3, where in cell D3, we total the number of rows to capture Phase 3 costs. Then, by copying the formulas in D4:D6 to E4:H6, I can compute total costs for Phases 1–3 for Drugs 2 through 5. For example, we find that for Drug 2, total Phase 1 costs equal $313, total Phase 2 costs equal $789, and total Phase 3 costs equal $876.
I run a small video store. In a worksheet, my accountant has listed the name of each movie and the number of copies in stock. Unfortunately, he combined this information into one cell for each movie. How can I extract the number of copies of each movie in stock to a separate cell?
The file Movies.xlsx, shown in Figure 20-5, contains the name of each movie and the number of copies in stock.

Figure 20-5: Movie example using the OFFSET function
We want to extract the number of copies owned of each movie to a separate cell. If the number of copies were listed to the left of a movie’s title, this problem would be easy. We could use the FIND function to locate the first space and then use the LEFT function to return all the data to the left of the first space. (See Chapter 5, “Text Functions,” for a discussion of how to use the LEFT and FIND functions, as well as other functions you can use to work with text.) Unfortunately, this technique doesn’t work when the number of copies is listed to the right of the movie title. For a one-word movie title, for example, the number of copies is to the right of the first space, but for a four-word movie title, the number of copies is to the right of the fourth space.
One way to solve this problem is to click the Data tab on the Ribbon, and in the Data Tools group, click Text To Columns to place each word in a title and the number of copies in separate columns. We can use the COUNTA function to count the total number of words in a title, including the number of items as a word, for each movie. We can then use the OFFSET function to locate the number of items.
To begin, insert enough columns to the right of the data to allow each word in the mov-ies’ titles and the number of items to be extracted to a separate column. I used six columns (Raiders of the Lost Ark requires six columns), as you can see in Figure 20-5. Then I select the cell range C2:C12 and on the Data tab, in the Data Tools group, click Text To Columns. I select Delimited in the Convert Text To Columns Wizard and use the space character as the delimiting character. After selecting cell D2 as the destination cell, I have the results shown in columns D through I of Figure 20-5.
Now we count the number of words in each movie’s cell (counting the number of items as a word), by copying from A2 to A3:A12 the formula COUNTA(D2:I2). The results are shown in Figure 20-5.
Finally, copying from B2 to B3:B12 the formula OFFSET(C2,0,A2), I can locate the number of copies of each movie in stock. This formula begins at the reference cell containing the movie title and moves over the number of columns equal to the number of “words” in the title cell. Because the reference cell contains only one cell, we can omit the height and width arguments of the OFFSET function so that the function uses only the cell containing the last “word” (the number of copies) of the title cell.
If you select any portion of a cell formula and then press F9, Excel displays the value created by that portion of the formula. You must press Esc or you will lose the formula. This trick makes it easier to debug and understand complex formulas. Thus, it might be easier to understand what the OFFSET portion of the formula does if you apply this trick to any of our formulas. For example, in file Offsetcost.xlsx cell E4 generates total Phase 1 cost with the formula =SUM(OFFSET(E10,0,0,E1,1)). If we move the cursor over OFFSET(E10,0,0,E1,1) and select F9, you will see =SUM({135,120,58}), which indicates that the OFFSET portion of the formula in cell D4 used the correct cells (D10:D12).
Another way to see how a complex formula works is to use the Evaluate Formula command. Move the cursor to E4 and click the Formulas tab on the Ribbon. In the Formula Auditing group, click Evaluate Formula. (See Figure 20-6.) Click the Evaluate button (it looks like a magnifying glass), and Excel simplifies the formula step by step until you see the formula’s final result. After clicking Evaluate twice, the formula appears as =SUM($E$10:E$12), so we know that in cell E4 we have selected the Phase 1 cells for Drug 2, which is what we wanted.

Figure 20-6: Evaluate Formula dialog box
How can I write a formula that always returns the last number in a column?
We often download new data into a worksheet. Can we write a formula that will always return sales during the most recent month? (See the file Mostrecent.xlsx and Figure 20-7 on the next page.)

Figure 20-7: Finding the last number in a column
Simply enter in cell D4 the formula OFFSET(B6,COUNT(B:B),0,1,1).
This formula begins in cell B6 and moves down a number of rows equal to the number of numerical entries in column B. This takes us to the most recent month of sales, which is selected because 1,1 returns only one cell.
How can I set up a range name that automatically includes new data?
We often add rows or columns of data to a range of data that is used to create a Pivot-Table or to perform another type of analysis. Usually, we simply update the range of cells referred to in our formula and then rerun our analysis. If we used dynamic range names, we will never have to update the range of data referred to in a formula or PivotTable. The range will automatically update. Here is an example.
In the file Dynamicrange.xlsx, we have an HR database. (See Figure 20-8.)

Figure 20-8: Example of a dynamic range
Currently, our data contains nine rows and four columns of data. Wouldn’t it be nice if we could create a range name that would automatically include more rows and/or columns when we add people or fields of information to our database?
To create a dynamic range, click the Formulas tab on the Ribbon, and in the Defined Names group, click Name Manager. Then define a range as shown in Figure 20-9.

Figure 20-9 Creating a dynamic range
Our range starts in cell A1 (we move 0 rows and columns from cell A1). Our selected range has number of rows=number of nonblank entries in column A and number of columns=number of nonblank entries in row 1. Thus, if we add people or data fields, the formula will automatically expand to include them. The dollar signs ($) are needed so the defined range will not shift if we move around the worksheet.
To try this out, enter the formula =SUM(data) in cell G14. At present, this formula totals all numbers in the range A1:D9 and yields $448,278.
Now add to row 10 the name Meredith, enter in B10 a salary of $10,000, enter in E1 a variable for Mistakes (add the word Mistakes), and in E10 enter 1000. The formula =SUM(data) now includes the 10,000 and 1000, and automatically updates to $459,278.
I am charting my company’s monthly unit sales. Each month I download the most recent month’s unit sales. I would like my chart to update automatically. Is there an easy way to accomplish this?
The workbook Chartdynamicrange.xlsx (see Figure 20-10) contains units sold of our company’s product. As you can see, the units sold have been charted using an XY (Scatter) chart.

Figure 20-10: We can use the OFFSET function to update this chart dynamically.
Beginning in row 19, we will download new sales data. Is there an easy way to ensure that the chart automatically includes the new data?
The key to updating the chart is to use the OFFSET function to create dynamic range names for both the Months column and the Units Sold column. As new data is entered, the dynamic range for unit sales will automatically include all sales data, and the dynamic range for months will include each month number. After creating these ranges, I modify the chart, replacing the data ranges used in the chart with the dynamic ranges. The chart will now be updated as new data is entered.
To begin, click Define Names on the Formulas tab of the Ribbon to display the New Name dialog box. Create a range named Units by filling in the dialog box as shown in Figure 20-11.

Figure 20-11: Creating a dynamic range name for the units sold
Entering =OFFSET('dynamic range'!$C$3,0,0,COUNT(!$C:$C),1) in the Refers To area of the dialog box creates a range one column wide beginning in cell C3, which contains the first unit sales data point. The range will contain as many numbers as there are in column C, which is derived by the portion of the formula that reads COUNT('dynamic range'!$C:$C). As new data is entered into column C, the data is automatically included in the range named Units.
Next, we create a dynamic range named Month for the months entered in column B. The formula is shown in Figure 20-12.

Figure 20-12: The formula used to define a dynamic range named Month
Now we go to our chart and click any point. In the formula box, you’ll see the formula SERIES('dynamic range'!$C$2,'dynamic range'!$B$3:$B$18,'dynamic range'!$C$3:$C$18,1). This formula is Excel’s version of the data originally used to set up the chart. Replace the ranges $B$3:$B$18 and $C$3:$C18 with our dynamic range names as follows: SERIES('dynamic range'!$C$2,dynamicrange.xlsx!Month,dynamicrange.xlsx!Units,1). Of course, if a blank space is listed above any new data, this method won’t work. Enter some new data and you’ll see that it is included in the chart.