Overview
- Given a list of baseball player salaries, how do I write a formula that yields the player with the highest salary? How about the player with the fifth highest salary?
- Given the annual cash flows from an investment project, how do I write a formula that returns the number of years required to pay back the project's initial investment cost?
Suppose you have a spreadsheet with 5000 rows containing 5000 names. You need to find the name John Doe, which you know appears somewhere (and only once) in the list. Wouldn't you like to know a formula that would return the row number at which John Doe is located? The Excel MATCH function enables you to find within a given array the first occurrence of a 'match' to a given text string or number. You should use the MATCH function instead of a LOOKUP function in situations in which you want the position of a number in a range rather than the value in a particular cell. The syntax of the match function is:
Match(lookup value, lookup range, match type)
In the explanation that follows, we'll assume that all cells in the lookup range are located in the same column. In this syntax,
- Lookup value is the value you're trying to match in the lookup range.
- Match type =1 requires your lookup range to consist of numbers listed in ascending order. The MATCH function then returns the row location in the lookup range (relative to the top of the lookup range) that contains the largest value in the range that is less than or equal to the lookup value. Match type = -1 requires the lookup range to consist of numbers listed in descending order. The MATCH function returns the row location in the lookup range (relative to the top of the lookup range) that contains the last value in the range that is greater than or equal to the lookup value. No matter in what order the values or text in the lookup range are listed (ascending, descending, or no order), match type = 0 returns the row location in the lookup range that contains the first exact match to the lookup value. When no exact match exists and match type = 0, Excel returns #N/A. The great majority of MATCH function applications use match type = 0, but if match type is not included, match type = 1 is assumed.
The file MatchEx.xls, shown in Figure 5-1, contains three examples of the MATCH function's syntax.
Figure 5-1: Examples of using the MATCH function to locate the position of a value in a range.
In cell B13, the formula MATCH("Boston",B4:B11,0) returns 1 because the first row in the range B4:B11 contains the value Boston. Notice that text values must be enclosed in quotation marks (''). In cell B14, the formula MATCH("Phoenix",B4:B11,0) returns 7 because cell B10 (the seventh cell in B4:B11) is the first cell in the range that matches 'Phoenix'. In cell E12, the formula MATCH(0,E4:E11,1) returns 4 because the last number that is less than or equal to 0 in the range E4:E11 is in cell E7 (the fourth cell in the lookup range). In cell G12, the formula MATCH(-4,G4:G11,-1) returns 7 because the last number that is greater than or equal to -4 in the range G4:G11 is contained in cell G10 (the seventh cell in the lookup range).
Unlike LOOKUP functions, the MATCH function can work with an inexact match. For example, the formula MATCH("Pho*",B4:B11,0) returns 7. The asterisk is treated as a wildcard, which means that Excel searches for the first text string in the range B4:B11 that begins with Pho.
If the lookup range is contained in a single row, Excel returns the relative position of the first match in the lookup range, moving left to right. As shown in the following examples, the MATCH function is often very useful when it is combined with other Excel functions, such as VLOOKUP, INDEX, or MAX.
Given monthly sales for several products, how do I write a formula that will calculate the sales of a product during a given month? For example, how much of product 2 did I sell during June?
The file ProductLookup.xls (shown in Figure 5-2) lists sales of four NBA bobble-head dolls for January through June. How can we write a formula that computes the sales of a given product during a given month? The trick is to use one MATCH function to find the row in which
Figure 5-2: The MATCH function can be used in combination with functions such as INDEX and VLOOKUP.
We have named the range B4:G7, which contains sales data for the dolls, as Sales. We enter the product we want to know about in cell A10 and the month we're examining in cell B10. In C10, we use the formula MATCH(A10,A4:A7,0) to determine which row number in the range Sales contains sales figures for the Kobe doll. Then, in cell D10, we use the formula MATCH(B10,B3:G3,0) to determine which column number in the range Sales contains June sales. Now that we have the row and column numbers that contain the sales figures we want, we can use the formula (INDEX(Sales,C10,D10)) in cell E10 to yield the piece of sales data we want. For more information on the INDEX function, see Chapter 4.
Given a list of baseball player salaries, how do I write a formula that yields the player with the highest salary? How about the player with the fifth highest salary?
The file Baseball.xls (see Figure 5-3) lists the salaries paid to 401 major league baseball players during the 2001 season. The data is not sorted in order of salary, and we want to write a formula that returns the name of the player with the highest salary as well as the name of the player with the fifth highest salary. To find the name of the player with the highest salary we proceed as follows:
- Use the MAX function to determine the value of the highest salary.
- Use the MATCH function to determine the row that contains the player with the highest salary.
- Use a VLOOKUP function (keying off the data row containing the player's salary) to look up the player's name.
We have named the range C12:C412, which includes the players' salaries, as Salaries. We've named the range used in our LOOKUP function (range A12:C412) as Lookup.
Figure 5-3: This example uses the MAX, MATCH, and VLOOKUP functions to find and display the highest value in a list.
In cell C9, we begin by finding the highest player salary ($22 million) with the formula MAX(Salaries). Next, in cell C8, we use the formula MATCH(C9,Salaries,0) to determine the 'player number' of the player with the highest salary. We use match type = 0 because the salaries are not listed in ascending or descending order. We find that player number 345 on the list has the highest salary. Finally, in cell C6, we use the function VLOOKUP(C8,Lookup,2) to look up the player's name in the second column of the lookup range. Not surprisingly, we find that Alex Rodriguez was the highest paid player in 2001.
To find the name of the fifth highest player, we need a function that yields the fifth largest number in an array. The LARGE function does that job. The syntax of the large function is LARGE(cell range, k). When the LARGE function is entered in this fashion, it returns the kth largest number in a cell range. Thus, the formula LARGE(salaries,5) in cell D9 yields the fifth largest salary ($12.6 million). Proceeding as before, we find that Derek Jeter is the player with the fifth highest salary. (The dl indicates that at the beginning of the season, Jeter was on the disabled list.)
Given the annual cash flows from an investment project, how do I write a formula that returns the number of years required to pay back the project's initial investment cost?
The file Payback.xls, shown in Figure 5-4, shows the cash flows for an investment project over the next 15 years. We assume that at time 0, the project required a cash outflow of $100 million. During year 1, the project generated a cash inflow of $14 million. We expect cash flows to grow at 10 percent per year. How many years will pass before the project pays back its investment?
Figure 5-4: In this example, we'll use the MATCH function to calculate an investment's payback period.
The number of years required for a project to payback an investment is called the payback period. In high-tech industries, payback period is often used to rank investments. (You'll learn in Chapter 8, however, that payback is flawed as a measure of investment quality because payback ignores the value of money over time.) For now, let's just concentrate on how to determine the payback period for our simple investment model.
To determine the payback period for the project, we proceed as follows:
- In column B we compute the cash flows for each year.
- In column C we compute the cumulative cash flows (total to date) for each year.
- We use the MATCH function (with match type =1) to determine the row number of the first year in which cumulative cash flow is positive. This calculation gives us the payback period.
We gave the cells in B1:B3 the range names listed in A1:A3. Our year 0 cash flow (-Initial_investment) is entered in cell B5. Our year 1 cash flow (Year_1_cf) is entered in cell B6. Copying from B7 to B8:B20 the formula B6*(1+Growth) computes the cash flow for years 2-15.
To compute the year 0 cumulative cash flow, we enter the formula B5 in cell C5. For later years we calculate cumulative cash flow by using a formula such as Year t cumulative cash flow = Year t - 1 cumulative cash flow + Year t cash flow. To implement this relationship, simply copy from C6 to C7:C20 the formula =C5+B6.
To compute the payback period, we use the MATCH function (with match type = 1) to compute the last row of the range C5:C20 containing a value less than 0. This calculation will always give us the payback period. For example, if the last row in C5:C20 that contains a value less than 0 is the sixth row in the range, that means the seventh value marks the cumulative cash flow for the first year the project is paid back. Because our first year is year 0, the payback occurs during year 6. Therefore, the formula in cell E2, MATCH(0,C5:C20,1) yields the payback period (6 years). If any cash flows after year 0 were negative, this method would fail because our range of cumulative cash flows would not be listed in ascending order.
No comments:
Post a Comment