MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)

• I have a list of distances between U.S. cities. How do I write a function that returns the distance between, for example, Seattle and Miami?

• Is there a way I can write a formula that references the entire column containing the distances between each city and Seattle?

Syntax of the INDEX Function

The INDEX function allows you to return the entry in any row and column within an array of numbers. The most commonly used syntax for the INDEX function is:

INDEX(Array,Row Number,Column Number)

To illustrate, the formula INDEX(A1:D12,2,3) would return the entry in the second row and third column of the array A1:D12. This entry is the one in cell C2.

• I have a list of distances between U.S. cities. How do I write a function that returns the distance between, for example, Seattle and Miami?

• The file Index.xlsx (see Figure 3-1) contains the distances between eight U.S. cities. The range C10:J17, which contains the distances, is named Distances.

  Figure 3-1: You can use the INDEX function to calculate the distance between cities.

• Suppose that you want to enter the distance between Boston and Denver in a cell. Because distances from Boston are listed in the first row of the array named Distances, and distances to Denver are listed in the fourth column of the array, the appropriate formula is INDEX(distances,1,4). The results show that Boston and Denver are 1991 miles apart. Similarly, to find the (much longer) distance between Seattle and Miami, you would use the formula INDEX(distances,6,8). Seattle and Miami are 3389 miles apart.

• Imagine that the Seattle SuperSonics basketball team is embarking on a road trip in which they play games in Phoenix, Los Angeles, Denver, Dallas, and Chicago. At the conclusion of the road trip, the Sonics return to Seattle. Can we easily compute how many miles they travel on the trip? As you can see in Figure 3-2, we simply list the cities the Sonics visit (8-7-5-4-3-2-8) in the order they are visited, starting and ending in Seattle, and copy from D21 to D26 the formula INDEX(distances,C21,C22). The formula in D21 computes the distance between Seattle and Phoenix (city number 7), the formula in D22 computes the distance between Phoenix and Los Angeles, and so on. The Sonics will travel a total of 7112 miles on their road trip. Just for fun, I used the INDEX function to show that the Miami Heat travel more miles during the NBA season than any other team.

  Figure 3-2: Distances for a Seattle Sonics road trip

• Is there a way I can write a formula that references the entire column containing the distances between each city and Seattle?

• The INDEX function makes it easy to reference an entire row or column of an array. If we set the row number to 0, the INDEX function references the listed column. If we set the column number to 0, the INDEX function references the listed row. To illustrate, suppose we want to total the distances from each listed city to Seattle. We could enter either of the following formulas:

• SUM(INDEX(distances,8,0))

• SUM(INDEX(distances,0,8))

• The first formula totals the numbers in the eighth row (Row 17) of the Distances array; the second formula totals the numbers in the eighth column (Column J) of the Distances array. In either case, we find the total distance from Seattle to the other cities is 15,221 miles, as you can see in Figure 3-1.