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.

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

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

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

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.

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

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.

10) Sort the students by Last Name.

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