Sample Excel Exam

In this exam, you will create an excel spreadsheet to manage a Grade Book.

1) Start Excel. Save the Workbook as Exam.xls. Start recording a macro, and name that Macro Exam.

Solution:
Start/Run/Excel
Ctrl-S
Exam.xls and press Enter

Not graded: Tools/Macro/Record New Macro
Type Exam and press Enter

2) Identify yourself. Select cell A13 and enter your name.

3) Formatting. In cell A1, enter Grade Book. Format the text, making it Bold, Italics, Fontface "Courier New" and Font Size 20, color Red. Merge and Center fields A1 through E1.

4) Change the name of worksheet 1 to Spring '06.

Right-click on the "Sheet 1" Tab at the bottom of the Screen. Choose rename from the pop-up menu.

5) Enter the Column Names.
The columns should be called First, Last, SSN, Midterm, Final, HW, Bonus, Total, Letter Grade. Skip a cell between SSN and Midterm.
Bold these column names.

6) Enter Data for 3 students: (You can make up alternate Names and SSNs.)
John Doe, 111-22-4321, 74, 89, No
Jane Smith, 222-12-1354, 56, 100, Yes
Sarah O'Conner, 123-66-2352, 86, 96, No

7) Calculate the HW Bonus.
In cell A12, type HW Bonus, and bold it. In cell B12, type the HW Bonus (in this case, 10).
For the first student, calculate the HW bonus (either 0 points or the points, depending on whether homework was done or not done.)
Apply the same formula to the next two students by dragging.

Solution: =IF(G3="Yes", $B$12, 0)
Drag the formula to the two cells below

8) Calculate the Total Grade. That is, the average of the midterm and final, plus the HW bonus. Apply to all students.

Solution: =average(E3, F3) + H3. Click and drag to apply formula.

9) Now we calculate the letter grade, using VLOOKUP.
0 to 59 = F
60 to 69 = D
70 to 79 = C
80 to 89 = B
90 to 100 = A

Create the table for this lower in the spreadsheet, and use VLOOKUP for the first student. Apply to all students.

Solution:
The first column should be the lower bound for getting the grade, the second column should be the letter grade. Assuming you placed this table from K18 to L22, the formula should be:
=VLOOKUP(I3,K18:L22,2), but then change the table references to absolute with F4.

10) Sort the students by Last Name.

Solution: Highlight the students, choose Data/Sort, and select which column to sort by.

11) Create a bar chart by Last Name and Final Grade. Delete the legend.

Solution: Select the Last and Total columns, click on the chart icon. Click finish. select the legend and press delete.

12) Stop recording the macro. Alt-F8, Edit, and email me the text of the macro.