Creating an Excel Workbook to Compute Test Scores

NEW PERSPECTIVES EXCEL 2010
TUTORIAL 3: CASE PROBLEM 1
CHEMISTRY 303
SKILLS
 Save a workbook with a new name
 Enter formulas
 Use absolute references
 Fill adjacent cells with formulas
 Create formulas using the COUNT function
 Create formulas using the MEDIAN function
 Create formulas using the MAX function
 Create formulas using the MIN function
 Apply conditional formatting to a range of cells
 Control page breaks
 Add print titles
 Modify the worksheet orientation
PROJECT OVERVIEW
Karen Raul is a professor of chemistry at a community college in Shawnee, Kansas. She has started using Excel to calculate the final grade for students in her Chemistry 303 course. The final score is a weighted average of the scores given for three exams and the final exam. The first three exams are each given a weight of 20 percent and the final exam is given a weight of 40 percent.
For an example of calculating weighted averages, consider the following four exam scores and weights:
Score Weight
• Exam 1 = 84 20%
• Exam 2 = 80 20%
• Exam 3 = 83 20%
• Final Exam = 72 40%
Karen prefers that you calculate the weighted average by multiplying each student's exam score by the weight given to the exam, and then totaling the results. The weighted average of the four scores above is:
Exam 1 Exam 2 Exam 3 Final Exam Weighted Average
84*0.2 + 80*0.2 + 83*0.2 + 72*0.4 = 78.2
Karen already entered the scores for her students and formatted much of the workbook. She wants you to enter the formulas and highlight the top 10 overall scores in her class.
STUDENT START FILE
NP_Excel2010_T3_CP1a_FirstLastName_1.xlsx (Note: Download your personalized start file from www.cengage.com/sam2010)
Instructions
1. Open the file NP_Excel2010_T3_CP1a_FirstLastName_1.xlsx and save the file as NP_Excel2010_T3_CP1a_FirstLastName_2.xlsx before you move to the next step. Verify that your name appears in cell B4 of the Documentation sheet. (Note: Do not edit the Documentation sheet. If your name does not appear in cell B4, please download a new copy of the start file from the SAM Web site.)
2. In the First Semester Scores worksheet, in cell F17, enter a formula to calculate the weighted average of the first student's four exams. The formula in cell F17 should use absolute references to the weights found in the range C8:C11, matching each weight with the corresponding exam score. (Note: Your weighted average formula should have the same general format as the weighted average formula described in the Project Overview.)
Use Auto Fill to copy the Formula in cell F17 into the range F18:F52.
3. In cell B5, use the COUNT function to calculate the total number of final scores in the range F17:F52.
4. In cell D8, use the MEDIAN function to calculate the median score for the first exam.
5. In cell E8, use the MAX function to calculate the maximum score for the first exam.
6. In cell F8, use the MIN function to calculate the minimum score for the first exam.
7. In cell G8, calculate the range of scores for the first exam, which is equal to the difference between the maximum and minimum score.
8. Calculate the median, maximum, minimum, and range scores for Exam 2, Exam 3, the Final Exam, and the Overall weighted score. (Hint: Repeat steps 4 through 7 for each of rows 9, 10, 11, and 12.)
9. Use conditional formatting to highlight the top 10 scores in the range F17:F52 with Light Red Fill with Dark Red Text.
10. Insert a page break at cell A14, repeat the first three rows of the worksheet in any printout, and verify that the worksheet is in portrait orientation.

Your completed worksheet should look like the Final Figure below. Save your changes, close the workbook and exit Excel. Follow the directions on the SAM Web site to submit your completed project.
 
FINAL FIGURE