Forbes Best Places for Business and Careers

Forbes Best Places for Business and Careers
Start with the Excel workbook (spreadsheet) Forbes Best Places for Business and Careers – start.xlsx.
Start this problem by opening up the starting file. Once you open the file, you will find information that has been obtained from Forbes.com regarding their 2013 analysis on the Best Places for Business and Careers, which can be found at the following website https://www.forbes.com/best-places-for-business/. In cells A1:F1, you will see the various column labels, such as Forbes Overall Rank in cell A1, Metro Area in cell B1, Population in cell C1, Cost of Business Rank in cell D1, Job Growth Rage in cell E1, and Educational Rank in cell F1. The dataset consists of information complied about the Top 200 reported in this investigation. Based on the initial analysis, we would like to summarize the data further. For example, there are four ranges that you will be asked summarize by implementing formulas that will contain INDEX and MATCH functions. These ranges are located in H1:M11, H13:J24, H26:J37, and H39:J50, and the tasks are described below. HINT: Do not be alarmed that most of the cells located in this spreadsheet are locked and cannot be edited.
1. In cell I2, write a formula that utilizes one INDEX and two MATCH statements that retrieves the Metro Area name found in column B that is ranked number 1 overall by Forbes, where the Forbes Overall Rank is located in column A. In other words, the INDEX will retrieve Metro Area in column B, the first MATCH will obtain the correct row based on the rank desired located in H2, and the second MATCH will obtain the correct column number based on the header value located in I1.
a. Write this formula in such a way that it can be copied or filled into all remaining cells within the range of I2:M11.
b. HINT: You will need to use a combination of absolute and relative reference.
2. In cell I15, write a formula that utilizes one INDEX and two MATCH statements that retrieves the Metro Area name found in column B that is ranked number 1 by Forbes in terms of the Cost of Business Rank, where the Cost of Business Rank is located in column D. In other words, the INDEX will retrieve Metro Area in column B, the first MATCH will obtain the correct row based on the rank desired located in H15, and the second MATCH will obtain the correct column number based on the header value located in I14.
a. Write this formula in such a way that it can be copied or filled into all remaining cells within the range of I15:J24.
b. HINT: You will need to use a combination of absolute and relative reference.
3. In cell I28, write a formula that utilizes one INDEX and two MATCH statements that retrieves the Metro Area name found in column B that is ranked number 1 by Forbes in terms of the Job Growth Rank, where the Job Growth Rank is located in column E. In other words, the INDEX will retrieve Metro Area in column B, the first MATCH will obtain the correct row based on the rank desired located in H28, and the second MATCH will obtain the correct column number based on the header value located in I27.
a. Write this formula in such a way that it can be copied or filled into all remaining cells within the range of I28:J37.
b. HINT: You will need to use a combination of absolute and relative reference.
4. In cell I41, write a formula that utilizes one INDEX and two MATCH statements that retrieves the Metro Area name found in column B that is ranked number 1 by Forbes in terms of the Education Rank, where the Education Rank is located in column F. In other words, the INDEX will retrieve Metro Area in column B, the first MATCH will obtain the correct row based on the rank desired located in H41, and the second MATCH will obtain the correct column number based on the header value located in I40.
a. Write this formula in such a way that it can be copied or filled into all remaining cells within the range of I41:J50.
b. HINT: You will need to use a combination of absolute and relative reference.