Lookup functions

Problems

1. The file Hr.xlsx gives employee ID codes, salaries, and years of experience. Write a formula that takes a given ID code and yields the employee’s salary. Write another formula that takes a given ID code and yields the employee’s years of experience.

2. The file Assign.xlsx gives the assignment of workers to four groups. The suitability of each worker for each group (on a scale from 0 to 10) is also given. Write a formula that gives the suitability of each worker for the group to which the worker is assigned.

3. You are thinking of advertising Microsoft products on a sports telecast. As you buy more ads, the price of each ad decreases as shown in the following table.

   Number of ads	Price per ad
   1–5	$12,000
   6–10	$11,000
   11–20	$10,000
   21 and higher	$9,000

For example, if you buy 8 ads, you pay $11,000 per ad, but if you buy 14 ads, you pay $10,000 per ad. Write a formula that yields the total cost of purchasing any number of ads.

4. You are thinking of advertising Microsoft products on a popular TV music program. You pay one price for the first group of ads, but as you buy more ads, the price per ad decreases as shown in the following table.

   Ad number	Price per ad
   1–5	$12,000
   6–10	$11,000
   11–20	$10,000
   21 or higher	$9,000

For example, if you buy 8 ads, you pay $12,000 per ad for the first 5 ads and $11,000 for each of the next 3 ads. If you buy 14 ads, you pay $12,000 for each of the first 5 ads, $11,000 for each of the next 5 ads, and $10,000 for each of the last 4 ads. Write a formula that yields the total cost of purchasing any number of ads. Hint: You probably need at least three columns in your table range, and your formula might involve two lookup functions.

5. The annual rate your bank charges you to borrow money for 1, 5, 10, or 30 years is shown in the following table.

   Duration of loan	Annual loan rate
   1 year	6%
   5 years	7%
   10 years	9%
   30 years	10%

   If you borrow money from the bank for any duration from 1 through 30 years that’s not listed in the table, your rate is found by interpolating the appropriate number between the rates given in the table. For example, let’s say you borrow money for 15 years. Because 15 years is one quarter of the way between 10 years and 30 years, the annual loan rate would be calculated as follows: .25*.09 + .75*.10.

Write a formula that returns the annual interest rate on a loan for any period between 1 and 30 years.

6. The distance between any two US cities (excluding cities in Alaska and Hawaii) can be approximated by the following formula:

   

The file Citydata.xlsx contains the latitude and longitude of selected US cities. Create a table that gives the distance between any two of the listed cities.

7. In the file Pinevalley.xlsx, the first worksheet contains the salaries of several employees at Pine Valley University, the second worksheet contains the age of the employees, and the third worksheet contains the years of experience. Create a fourth worksheet that contains the salary, age, and experience for each employee.

8. The file Lookupmultiplecolumns.xlsx contains information about several sales made at an electronics store. A salesperson’s name will be entered in B17. Write an Excel formula that can be copied from C17 to D17:F17 that will extract that salesperson’s radio sales to C17, TV sales to D17, printer sales to E17, and CD sales to F17.

9. The file Grades.xlsx contains student’s grades on an exam. Suppose the curve is as follows:

   Score	Grade
   Below 60	F
   60–69	D
   70–79	C
   80–89	B
   90 and above	A

Use Excel to return each student’s letter grade on this exam.

10. The file Employees.xlsx contains the ranking each of 35 workers has given (on a 0–10 scale) to three jobs. The file also gives the job to which each worker is assigned. Use a formula to compute each worker’s ranking for the job to which the worker is assigned.

11. Suppose one dollar can be converted to 1,000 yen, 5 pesos, or 0.7 euros. Set up a spreadsheet in which the user can enter an amount in US dollars and a currency, and the spreadsheet converts dollars to the entered currency.

12. The file Qb2013.xlsx gives NFL quarterback (QB) statistics for the 2013 season. Write formulas in cells J2 and K2 that return the QB’s TDs and interceptions when you type a QB’s name in cell I2.

13. The file NBAplayers.xlsx gives the age and salary for several NBA players. Enter formulas in cells J5:K50 that return each player’s age and salary.

14. Column F of the file Hardware.xlsx gives product codes for hardware products, and Column G gives the price of each product. Columns M–O list the quantity and price a hardware store paid for various products. Determine the total cost of the hardware store’s purchases.