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.

   Open table as spreadsheet

   Number of ads	Price per ad
   1–5	$12,000
   6–10	$11,000
   11–20	$10,000
   More than 20	$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.

   Open table as spreadsheet

   Ad number	Price per ad
   1–5	$12,000
   6–10	$11,000
   11–20	$10,000
   20 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 will 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.

   Open table as spreadsheet

   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 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:

   

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

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

   

   The file Citydata.xlsx contains the latitude and longitude of selected U.S. 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 each salesperson’s Radio sales to C17, TV sales to D17, Printer sales to E17, and CD sales to F17.