The OFFSET Function in Microsoft Excel 2010

Questions answered in this chapter:

• 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 does Excel’s Evaluate Formula feature work?

• 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).

• 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.

Answers to This Chapter’s Questions

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 22-1, provides some examples of the OFFSET function in action.

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. It moves one row up and one column to the right, which brings us to cell C6. The formula now selects 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 22-2 work the same way. In the following sections, I’ll show you how to apply the OFFSET function to 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 22-2 (see the workbook Lefthandlookup.xlsx), I listed the members of the 2002–2003 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.

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, I 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, I 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 22-3) contains data for units sold, sales revenue, and variable cost for software sold to several countries in Asia and the Middle East. Each month, when you download the monthly financial reports, the location of each country in the worksheet changes, so you need formulas that always return (for a given country) the correct units sold, revenue, and variable cost.

By copying from D21 to E21:F21 the formula OFFSET($C$6,MATCH ($C21,$C$7:$C$17,0),D20), you can compute the result you want. This formula sets reference equal to cell C6 (which contains the words Country/Region). 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 you 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 you 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 22-4.

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 I 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 me to the beginning of Phase 3, where in cell D3, I 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, 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 22-5, contains the name of each movie and the number of copies in stock.

I 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. I 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 6, “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. You 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. You 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 movies’ 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 22-5. Then I select the cell range C2:C12, and click Text To Columns on the Data tab. 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 22-5.

Now I 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 22-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, I 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.

How does Excel’s Evaluate Formula feature work?

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 lose the formula. This trick makes debugging and understanding complex formulas easier. Thus, it might be easier to understand what the OFFSET portion of the formula does if you apply this trick to any of the formulas in this chapter. For example, in the file Offsetcost.xlsx, cell E4 generates total Phase 1 cost with the formula =SUM(OFFSET(E10,0,0,E1,1)). If you move the cursor over OFFSET(E10,0,0,E1,1), highlight this part of the formula, and then select F9, you will see =SUM({135,120,58}), which indicates that the OFFSET portion of the formula in cell D4 uses the correct cells (D10:D12). Be sure you press Esc to exit this procedure or you will lose your formula!

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 22-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 you know that in cell E4 you have selected the Phase 1 cells for Drug 2, which is what you wanted.

How can I write a formula that always returns the last number in a column?

You often download new data into a worksheet. Can you write a formula that always returns sales during thelas most recent month? (See the file Mostrecent.xlsx and Figure 22-7.)

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 you 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?

Users of Excel often add rows or columns of data to a range of data that is used to create a PivotTable or to perform another type of analysis. Usually, they simply update the range of cells referred to in their formula and then rerun the analysis. However, you can use dynamic range names and never have to update the range of data referred to in a formula or PivotTable. The range will automatically update. Here is an example.

The file Dynamicrange.xlsx shows entries from an HR database. (See Figure 22-8.)

Currently, the data contains nine rows and four columns of data. Wouldn’t it be nice if you could create a range name that would automatically include more rows and/or columns when you add people or fields of information to the database?

To create a dynamic range, click the Formulas tab and, in the Defined Names group, click Name Manager. Then click New and define a range as shown in Figure 22-9.

The range starts in the upper-left corner: cell A1. Next we move 0 rows and columns from cell A1. The 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 you add people or data fields, the formula will automatically expand to include them. The dollar signs ($) are needed so that the defined range will not shift if you 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 1,000. The formula =SUM(data) now includes the 10,000 and 1,000, 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 22-10) contains the units sold of your company’s product. As you can see, the units sold have been charted using an XY (Scatter) chart.

Beginning in row 19, you 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, you can modify the chart, replacing the data ranges used in the chart with the dynamic ranges. The chart now updates as new data is entered.

To begin, click Define Names on the Formulas tab on the ribbon to display the New Name dialog box. Create a range named Units by filling in the dialog box as shown in Figure 22-11.

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, create a dynamic range named Month for the months entered in column B. The formula is shown in Figure 22-12.

Now go to the 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 the dynamic range names as follows: SERIES(‘dynamic range’!$C$2,Chartdynamicrange.xlsx!Month,Chartdynamicrange.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.

Remarks

The Excel table feature makes it easy to set things up so that charts and formulas automatically incorporate new data. See Chapter 26, “Tables” for a discussion of this feature.