# CMPS 1200 - Week 7 Homework »

### Using Excel Functions

##### Please click on the 'Lab Manual' for detailed instructions.

### Exercise 1

- Create a new Excel workbook named hw7.xlsx.
- Name the first sheet of the workbook Exercise 1.
- Enter the following into the Exercise 1 worksheet:

A B C 1 Bakersfield Temperatures 2 Week of July 15 3 4 Day High Low 5 5 105 70 6 6 106 71 7 7 108 71 8 8 101 69 9 9 100 72 10 10 105 78 11 11 107 80 12 13 Average High 14 Average Low 15 Maximum 16 Minimum

- Use the Average function in cell C13 to find the average of High.
- Use the Average function in cell C14 to find the average of Low.
- Use the Maximum function in cell C15 to find the maximum of High.
- Use the Minimum function in cell C16 to find the minimum of Low.
- Format the averages with 1 decimal place.
- Randomly put your full name in any cell on problem 1.

### Exercise 2

A B 1 Car Payments 2 3 Car Price 35,000 4 Years 5 5 6 Interest Rate Payment 7 0.03 8 0.0325 9 0.035 10 0.0375 11 0.04 12 0.0425

- Use the PMT function to find the monthly payments for each of the different interest rates.
- The price of the car and number of years should be
*absolute references*. - Center the title across both columns.
- Format the interest rate as a percentage with 2 decimal places.
- Center the interest rates in the column.
- Randomly write the last 4 digits of your ID in any cell for problem 2.

### Exercise 3

- Using the same workbook on a new worksheet named Exercise 3 enter this table:

A B C D E 1 Car Rental 2 3 Car # Miles Days Rate/Day Total 4 5 CH356 240 5 6 F0753 187 3 7 TO221 322 8

- If the car is rented for more than three days (days > 3) the Rate/Day is $45.00.
- Otherwise, it is $55.00. Use an IF function to determine the correct rate for each customer.
- The Total charge is the ((number of Days * the Rate/Day) + (.35 * the number of Miles)).
- Use a formula to find the Total.
- Format monetary amounts to two decimal places.
- Right align the column headings for columns that contain numbers.
- Center the title.
- Type your favorite city in a random column.

### Exercise 4

- Using the same workbook add a new sheet and name it 'Exercise 4'. Enter this table:

A B C D E F 1 Income Taxes 2 3 Income State Federal Total 4 Rate Amount Rate Amount 5 10000 6 20000 7 30000 8 40000 9 50000 10 60000 11 70000 12 80000

- Use an IF function to find the state tax rate in column B.
- If the income is less than $55,000, the state tax rate is 7% (.07).
- If the income is $55,000 or more, the tax rate is 12% (.12).
- The amount of the State tax is the State Rate times the Income.
- Use a formula to find the amount of the State tax.
- The Federal tax rate is .20 if the income is less than 50,000.
- The Federal tax rate is .35 if the income is 50,000 or more.
- Use an IF function to find the Federal Rate in column D.
- Use a formula in column E to find the amount of the Federal tax.
- Add the State and Federal taxes together to find the Total.

### Exercise 5

- Using the same workbook add a new sheet and name it 'Exercise 5'. Enter this table:

A B C D E F 1 First Quarter Sales Report 2 3 Training Agent Sales Commission Bonus TotalPay 4 Jones 422,750 5 Smith 574,329 6 Calway 302,709 7 Browne 145,876 8 Ames 685,241 9 Lewis 224,857

- Each agent receives a commission of 5% of their sales (sales*.05).
- Use a formula to fill in column D.
- Each agent with sales of more than 400,000 (sales > 400000) receives an extra bonus of 2% of their total sales (sales * .02).
- If the sales are less than 400,000, the bonus is 0.
- Use an IF function to determine the bonus in column E.
- Use a formula to find the Total Pay in column F (commission + bonus).
- Finally, any agent with sales less than 225,000 (sales < 225000) is required to attend a training class.
- Use an IF function to put an exclamation point (!) in column A if an agent must go to the class. Remember to use "double quotes" around non numeric data and around any empty space.

### Exercise 6

- Using the same workbook create a new worksheet named
*'Commute'*. Enter this table:

A B C D E F 1 Getting to Work in the City 2 3 City State Workers Time(min) Public Trans(%) Drive Self(%) 4 New York NY 3,183,088 36.5 53.4 24 5 Los Angeles CA 1,629,096 26.5 10.5 65.2 6 Chicago IL 1,181,677 31.5 29.7 46.3 7 Pitsburg PA 640,577 27.4 28.7 44.7 8 San Diego CA 560,913 20.4 4.2 70.7 9 Detroit MI 325,054 24.7 10.7 67.8 10 Sacramento CA 500,566 24.0 6.7 72.5

- Create a new worksheet called Sort1.
- Copy the 'Commute' table into 'Sort1'. The sort the table with the city names in reverse alphabetical order.

- Create a new sheet called Sort2.
- Copy the table from the 'Commute' worksheet into 'Sort2' and sort it by percentage of workers using public transportation (Public Trans) in descending order.

- Create a new sheet called Sort3.
- Copy the 'Commute' table from the first sheet into 'Sort3' and sort it with the States in reverse alphabetical order and with the Cities in each state in alphabetical order.

- Create a new sheet called
*'Lists'*. - In cell A1 enter California Cities.
- Use the
*original 'Commute' table*and filter out the cities that are not in California. - Copy the filtered information, including columns A through F and the column headings to cell A2 in the 'Lists' worksheet.

- On the
*Lists*sheet, enter Most Drivers in cell A10. - Use the original 'Commute' table and filter out the cities where more than 65% of the workers drive themselves to work (Drive Self). This means that the 'Commute' table will show only the cities where 65% or more of the people drive themselves to work.
- Copy the filtered information to cell A11.

- In cell A25 of the 'Lists' sheet, enter Fastest Cities.
- Use the original 'Commute' table and filter out the cities where the average time (Time) to get to work is less than 25 minutes. Only the cities where the commute is less than 25 minutes are shown.
- Copy the filtered information to cell A26.

Save the Excel workbook as 'hw7.xlsx' in the 'Excel Workbook' format (it should be default). Send hw7.xlsx as an email attachment to your TA.

In the subject line for your email, include the assignment number, your name and the last 3 digits of your RunnerID in this form:

Homework 7, 123, Samantha Spade

(Replace Samantha Spade with your name and 123 with the
last three digits of your student ID). The subject line
must be exactly in this format (including capital letters
and commas) in order for my grading script to download
your assignment correctly, so **please follow these
instructions carefully.**

Errors in your homework grade must be worked out with your TA within one week of grades being posted. You will be asked to show the assignment in the sent email box with the attachment to the TA as proof that you submitted your work on time.

Remove your USB drive and take the drive with you when you leave. This should be the last action when you leave the lab every time. The lab computers are wiped clean every night and all data stored on them is deleted. USB drives left plugged into the computer usually find a new owner.

##### Homeworks

DUE: Monday after homework is assigned. Email your work to your TA.

NO LATE WORK ACCEPTED.

NO WORK DROPPED.

##### Grades

Posted by last 3 digits of your RunnerID (see TA if you wish to change this).

CHECK GRADES WEEKLY!!

Errors must be worked out with your TA within ONE WEEK of posting.