Homework Assignments

Homework 1, Problem 1 (due September 10)

1. Create a workbook named LastnameHW1Problem1.xls.
2. Create on sheet1 a table of exam scores for 4 students and 3 exams labeled by Exam1, Exam2, and Exam3.
3. Above the table define a scale factor of 1.2 which is used to multiply all the Exam3 scores to produce a new column Exam3*.
4. For each student, compute the total exam score using the adjusted scores for Exam3 and using relative reference.
5. For each exam, compute the average exam score using relative reference.

Homework 2, Problem 2 (due September 22)

1. Create a workbook named LastnameProblem02.xls.
2. Create on sheet1 a table of exam scores for 4 students and 3 exams .
3. The scores for each student should be placed on a separate row labeled by the name of the student.
4. The scores for each exam should appear on a separate column label by the exam number.
5. Use Names instead of References to various cell ranges.
6. For each student, compute the total exam score using the appropriately defined Name rather than using references.
7. For each exam, compute the average exam score using the appropriately defined Name rather than using references.

Homework 2, Problem 3 (due September 22)

1. Create a workbook named LastnameProblem03.xls.
2. Create a Macro on a sheet named FactoryItems so that you can enter a table of items manufactured by a total of 3 factories for each quarter within a year (but do not actually enter any numbers yet in your Macro). The factories are located in New York, Atlanta and San Francisco.
3. The items made by each factory should be placed on a separate row labeled by the factory’s location.
4. The number of items made by the factories in a given quarter should appear on a separate column label by the quarter.
5. For each factory, create a formula to compute the total number of item produced in a year (using relative references).
6. For each quarter, create a formula to find the lowest number of items made by any of the three factories (using relative references).
7. Save your Macro, name it "CreateTable" and define a hot key for it.
8. Next erase everything that you have typed.
9. Execute the Macro using the hot key.
10. Enter the number of items in the table and watch how your sheet behaves.
11. Press Alt-F11 to read the VBA codes that you have created with your Macro.
12. Press Alt-F11 to toggle back to Excel.

Homework 3, Problem 4 (due September 29)

Write a VBA Sub procedure to figure out the amount of change to be returned after a customer bought a certain amount of grocery. Assume that the total purchase to be input by the user is less than 10 dollars. Assume that the customer pays with a 10 dollar bill. The problem is to figure out the amount of change to be returned to the customer.

Compute and display the number of one dollar bills, quarters, dimes, nickels and pennies to be returned to the customer. To make the problem easier, don't use five dollar bills.

You should insert comments in your code for documentation.

Homework 4, Problem 5 (due October 6)

For any given point in 2D, write a VBA Sub procedure to convert the coordinates from Cartesian to polar form. Input the x and y coordinates of the point separately using an InputBox. The output angle must be given in degrees, and is required to be in the semi-open interval: [0, 360). There must be no negative angles; and no angles greater than or equal to 360 degrees. Make sure that your program works for any point in the finite 2D plane. Check for any special or usual input point. You may use these two functions: Sqr (the positive square root) and Atn (the arctan of a number measured in radian) in your program. Display the results together with the original Cartesian coordinates of the given point in a message box.

You must use Options Explicit and declare all your variables. Any constant must be declared as such. As usual, insert adequate comments in your program.