Excel 4 Ur Business

Overview

• How can I create a reference to a rectangular 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 by keying off the right-most column instead of the left-most column in a table range?
• I often download sales of a software product 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 spreadsheet. Can I create a formula that will always pick up 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 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?
• I run a small video store. In a spreadsheet, my accountant has listed the name of each movie in stock and the number of copies in stock. Unfortunately, for each movie this information is in a single cell. How can I extract the number of copies of each movie in stock to a separate cell?
• 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 automatically update. Is there an easy way to accomplish this?

The OFFSET function is used to create a reference to a rectangular 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 to Excel the number of rows and column away from the reference cell that you want to create your rectangular 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. The OFFSET function is very useful because you can copy it as you can any other Excel function, and the specified number of rows and columns you move from a reference cell can be calculated 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.
• Rows moved is the number of rows away from the reference 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 that you want the range reference to start (the upper left cell in the offset range). 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 is an optional argument that gives the number of rows in the offset range. Width is an optional argument that gives the number of 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 reference.

How can I create a reference to a rectangular range of cells that is a specified number of rows and columns from a cell or another range of cells?

The file OffsetExample.xls, shown in the following figure, provides some examples of the OFFSET function in action.

For example, in cell B10 I entered the formula SUM(OFFSET(B7,-1,1,2,1)). This formula begins in cell B7. We move up one row and one column to the right, which brings us to cell C6. We now pick out a range consisting of 2 rows and 1 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 the following fugure work the same way. In the following sections, we’ll apply the OFFSET function to solve some problems that were sent to me by MBAs working at major U.S. companies.

How can I perform a lookup operation keying off the right-most column instead of the left-most column in a table range?

In the following figure, I’ve listed the members of the Dallas Mavericks NBA team and their field goal percentages. If I’m asked to ‘look up’ the player with a given 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 a snap if you combine the MATCH and OFFSET functions.

I enter the player’s name in cell D7. Then I use a reference cell of B7 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 portion MATCH(D7,$C$8:$C$22,0) of the formula OFFSET(B7,MATCH(D7,$C$8:$C$22,0),0) picks up the number of rows below row 7 where our player’s name occurs. Then we move that many rows below row 7 and move over no columns from column B. Because the reference consists of a single cell, omitting the height and width arguments of the OFFSET function ensures that the range returned by this formula consists of one row and column (a single cell). Thus we pick up the player’s field goal percentage.

I often download sales of a software product 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 spreadsheet. Can I create a formula that will always pick up Iran’s revenues, costs, and units sold? The file AsianSales.xls (see in the following figure) 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 financials, the location of each country in the spreadsheet changes, so we want formulas that will 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) 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 (the value in cell D20) to pick up units sold and down the number of rows that match the number of rows below row 6 in which the country listed in C21 is found. 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 and pick up 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 and pick up 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.xls contains the monthly costs incurred in developing five drugs. Each drug goes through three stages of development. For each drug, the data also includes the number of months required to complete each phase. A subset of the data is shown in the following figure.

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 first-phase 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 pick up all phase 2 costs. Finally, in cell D6, I pick up the phase 3 development costs for drug 1 with 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, and we add up the number of rows in cell D3 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 spreadsheet, my accountant has listed the name of each movie in stock and the number of copies in stock. Unfortunately, for each movie this information is in a single cell. How can I extract the number of copies of each movie in stock to a separate cell?

The file Movies.xls, shown inthe following figure, contains the movies in stock and the number of copies of each movie in stock.

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 pick up all the data to the left of the first space. (See 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 use the Data, Text To Columns command to place each word in a title and the number of copies in separate columns. We can then 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 ‘pick off’ 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 6 columns), as you can see inthe following figure. Then I select the cell range C2:C12 and chose Data, 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 inthe following figure.

Now we count the number of words, including the number of items as one word, for each movie by copying from A2 to A3:A12 the formula COUNTA(D2:I2). The results are shown in the following figure.

Finally, copying from B2 to B3:B12 the formula OFFSET(C2,0,A2), I can pick up the number of copies of each movie in stock. This formula begins at the cell reference containing the movie title and moves over the number of columns equal to the number of ‘words’ in the title cell. Because the cell reference contains only one cell, we can omit the last two arguments of the OFFSET function so that the function picks up only the single cell containing the last ‘word’ (which is the number of copies) of the title cell.

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 DynamicRange.xls (see the following figure) contains units sold for our company’s product. As you can see, the units sold have been charted using an XY (Scatter) chart.

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 a dynamic range name 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, select Insert, Name Define and create a range named Units by filling in the dialog box as shown in the following figure.

Entering =OFFSET(’dynamic range’!$C$3,0,0,COUNT(’dynamic range’ !$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 units 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 Months for the months entered in column B. The formula is shown in the following figure.

Now we go to our chart and click on any selected 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 used to originally 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.xls!Month,dynamicrange.xls!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 the data is included in the chart.

 

 

 

 

 

 

 

 

 

 

 

Explore posts in the same categories: MS Excel Tune Up