The OFFSET Function in Microsoft Excel 2010

Problems

1. The file C22p1.xlsx contains data about unit sales for 11 products during the years 1999–2003. Write a formula using the MATCH and OFFSET functions that determines the sales of a given product during a given year. Can you think of another way to solve this problem without using the MATCH and OFFSET functions?

2. A commonly suggested moving average trading rule is to buy a stock when its price moves above the average of the last D months and to sell it when its price moves below the average of the last D months. In Chapter 12, “IF Statements”, I showed that for D=15, this trading rule outperformed the Standard &Poor’s 500 by a substantial amount. By combining a one-way data table with the OFFSET function, determine the value of D that maximizes trading profit (excluding transactions costs). You can find pertinent data in the file Matradingrule.xlsx.

3. A commonly suggested moving average trading rule is to buy a stock when its price moves above the average of the last B months and to sell it when its price moves below the average of the last S months. In Chapter 13, “Time and Time Functions”, I showed that for B=S=15, this trading rule outperformed the Standard & Poor’s 500 by a substantial amount. By combining a two-way data table with the OFFSET function, determine the values of B and S that maximize trading profit (excluding transactions costs). You’ll find data for this problem in the file Matradingrule.xlsx.

4. The file Lagged.xlsx contains data about the number of magazine ads placed by U.S. Army Recruiting during each of 60 consecutive months. For each month, the k-month lagged number of ads is defined to equal the number of ads placed k months ago. For months 7–60, you want to compute the 1-month lagged, 2-month lagged, through 6-month lagged values of the number of ads. Use the OFFSET function to efficiently compute these lagged values.

5. The file Verizondata.xlsx gives sales of four different Verizon phones in five regions. Determine an efficient method to enter for each of the 20 region-product combinations, the region, type of phone, and sales of each phone into one row.

6. This problem is a difficult one. The file Agingdata.xlsx gives the number of insurance claims projected to be received daily and the number of insurance company workers available. Each day, a worker can process up to 30 claims. Workers process the oldest claims in the system first. Cells H6:AL6 contain the number of claims already in the system on January 1, before new claims arrive. Set up a worksheet to track the “aging” of the claims. That is, for each day, how many 1-day old, 2-day old, ... 30-day old, and over 30-day old claims will be in the system.

7. Each row of the file Tapesales.xlsx contains monthly sales of a video tape. Write a formula to determine sales for each tape during its first six months on the market.

8. To obtain a golfer’s handicap, you average the 10 lowest of the golfer’s last 20 rounds. Then you subtract 80 and round to the nearest integer. Thus, if the 10 lowest of the last 20 rounds adds up to 864, the handicap would be 6. The file Golfdata.xlsx contains a golfer’s scores. Beginning in row 24, compute the golfer’s handicap after each round. Assume that if the tenth best score in the last 20 rounds occurs more than once, then all rounds including that score will be included in the handicap calculation. Note that the Excel function =ROUND(x,0) will round x to the nearest integer.

9. Each row of the file Carsumdata.xlsx contains sales data for a product (car, train, or plane) from January thru July. Suppose you enter a month and a product into the worksheet. Write a formula that gives the total sales of that product during the given month.

10. The file Verizon.xlsx contains monthly returns on Verizon stock. Use the OFFSET function to extract all the January returns to one column, all the February returns to one column, and so on.

11. The file Casesensitive.xlsx contains product codes and product prices. Note that the product codes are case sensitive. For example, DAG32 is not the same product as dag32. Write a formula that gives the product price for any product code. Hint: You might need to use the EXACT function. The formula EXACT(cell1,cell2) yields True if cell1 and cell2 have exactly the same contents. EXACT differentiates between uppercase and lowercase letters.

12. The file Reversed.xlsx contains a column of numbers. Use the OFFSET function to “reverse” the numbers so that the number on bottom occurs on top, and so on.

13. The file Diagonal.xls contains a matrix of numbers. Determine how to put the diagonal elements of the matrix in a single column.

14. The file Yeartodate.xlsx contains a company’s monthly sales during the years 2008–2014. Write a formula that returns for a given year and month of a year, the year’s cumulative sales to date. For example, if you enter a 6 for the month and 2010 for the year, your formula should compute total sales for the period January–June 2010.

15. Show how you might create a graph of monthly sales that always displays just the last six months of sales.

16. The file Transactiondata.xlsx contains sales transactions in divisions A, B, C, D, and E of a drug company. Use the OFFSET function to modify this data so that the sales for each division appear in a single row.