You want to analyze the locations of 12 stores in a region. The region has been divided into 72 zones in an 8-by-9 grid. Each grid has a certain number of potential customers who could shop in the store of that zone. The cells of the table that contains this data has been colored by color scales of Excels Conditional Formatting tool (the darker the shade of green the larger the number of potential customers in that zone). You have a list of the stores with their coordinates (in range A1:C15) and you would like to visualize the locations of these stores in the grid. To this end: a) In the column titled Code get a code that is obtained by concatenating the x- coordinate and y-coordinate of each store. Hint: You can use the operator & to join or concatenate the contents of two cells into single text. So, for example, the formula =B2&C2 will create a text that joins the contents of these cells. b) In the column titled Customers in zone, find the number of customers in the zone of that store. Hint: Use INDEX. c) Calculate the total number of customers covered by the stores in cell E14 and in cell E15 calculate the value you have calculated in E14 as percent of the total number of potential customers in all zones in the region. d) In the matrix titled Store Locations you want to see the location of each store as shown in the screen shot below. (Hint: You can use MATCH, INDEX and IFERROR functions). For the rest of the question, assume that a store in a location can cover its own region and its adjacent neighbors. So for instance Store 7 can cover the customers in zones with (x,y) coordinates (3,1), (3,2), (3,3), (4,1),(4,2), (4,3), (5,1), (5,2), and (5,3).

e) In the table titled Number of Stores Covering Each Zone you should calculate for each zone the number of stores that have that zone as their adjacent neighbors. For instance, zone in coordinates (3,1) is covered by Store 1 and Store 7, so your formula should return 2. To do this you will need to use two functions: COUNTIF and OFFSET. Hint 1: When entering the condition for the COUNTIF (or any conditional function for that matter), you can use the wildcard character * to create a condition that looks for any string that starts with St by writing COUNTIF(a range reference, St*). This means Excel will count the number of cells that have a text content that start with St. In general, you can use * to stand for any character. Another example would be *re* represents all text that contains re. Hint 2: You will use OFFSET to return a range reference (address) in which COUNTIF will look for St*. f) In the table titled Store Locations use conditional formatting so that zones (cells) covered by at least 1 store have a green fill-color, whereas others are left without as they are. Note that the conditional formatting here will be using information in the table titled Number of Stores Covering Each Zone. g) In cell D26 calculate the total customers covered note that customers in a zone are covered if that zone is covered by at least one store. Hint: You will use SUMIF h) In cell D27 calculate the value you have found in cell D26 as a percent of total number of customers.

Store Locations X-Coordinates 1 2 1 Store 1 2. 3 4 5 6 7 8 9 Store 5 Store 10 Store 7 3 Store 9 Store 3 4 Y-Coordinates 5 Store 13 Store 2 6 7 Store 14 Store 4 Store 8 Store 12 8 Store 11 Store 6 9 A B C H J KL M N 0 P Customers in zone 4 0 11200 1700 1400 300 0 700 1200 500 Number of potential customers in each zone X-Coordinates 1 2. 3 1 200 1500 0 2 300 600 100 3 300 0 3400 4 100 1100 1800 5 800 300 3800 6 800 200 500 7 100 400 8 200 1600 2000 9 100 1100 3700 Store Locations X-Coordinates 1 2 3 1 2 3 4 5 61 5 100 900 2800 600 100 800 2700 700 100 6 400 500 300 200 800 1500 200 3300 200 7 500 1300 400 1300 1600 400 900 100 1200 8 100 3000 200 100 3100 2500 1400 1200 200 9 9 100 100 100 500 900 900 2300 4100 800 1100 4 5 6 7 8 1 Store X-Coord Y-Coord Code 2 Store 1 2 1 3 Store 2 5 5 4 Store 3 8 3 5 Store 4 4 7 6 Store 5 6 1 7 Store 6 8 8 8 Store 7 4 2 9 Store 8 6 7 10 Store 9 7 3 11 Store 10 9 9 1 12 Store 11 7 8 13 Store 12 2 5 14 Total 15 Total as % 16 17 18 19 20 21 22 23 24 25 26 Total customers covered 27 Total customers covered % 28 29 30 31 32 33 34 35 36 37 9 Y-Coordinates 7 81 9 3 4 5 6 7 8 9 Number of Stores Covering Each Zone X-Coordinates 2 1 2 3 4 Y-Coordinates 5 6 7 8 9 9 38 Store Locations X-Coordinates 1 2 1 Store 1 2. 3 4 5 6 7 8 9 Store 5 Store 10 Store 7 3 Store 9 Store 3 4 Y-Coordinates 5 Store 13 Store 2 6 7 Store 14 Store 4 Store 8 Store 12 8 Store 11 Store 6 9 A B C H J KL M N 0 P Customers in zone 4 0 11200 1700 1400 300 0 700 1200 500 Number of potential customers in each zone X-Coordinates 1 2. 3 1 200 1500 0 2 300 600 100 3 300 0 3400 4 100 1100 1800 5 800 300 3800 6 800 200 500 7 100 400 8 200 1600 2000 9 100 1100 3700 Store Locations X-Coordinates 1 2 3 1 2 3 4 5 61 5 100 900 2800 600 100 800 2700 700 100 6 400 500 300 200 800 1500 200 3300 200 7 500 1300 400 1300 1600 400 900 100 1200 8 100 3000 200 100 3100 2500 1400 1200 200 9 9 100 100 100 500 900 900 2300 4100 800 1100 4 5 6 7 8 1 Store X-Coord Y-Coord Code 2 Store 1 2 1 3 Store 2 5 5 4 Store 3 8 3 5 Store 4 4 7 6 Store 5 6 1 7 Store 6 8 8 8 Store 7 4 2 9 Store 8 6 7 10 Store 9 7 3 11 Store 10 9 9 1 12 Store 11 7 8 13 Store 12 2 5 14 Total 15 Total as % 16 17 18 19 20 21 22 23 24 25 26 Total customers covered 27 Total customers covered % 28 29 30 31 32 33 34 35 36 37 9 Y-Coordinates 7 81 9 3 4 5 6 7 8 9 Number of Stores Covering Each Zone X-Coordinates 2 1 2 3 4 Y-Coordinates 5 6 7 8 9 9 38