Case Study
Introduction to Economics Alice Keyes teaches Introduction to Economics 102 at Mountain View Business School in Huntington, West Virginia. She wants to use Excel to track the grades from her class. Alice has already enters the homework, quiz, and final exam scores for all of her students in a workbook, and she has asked you to set up the formulas and functions for her.
You will calculate each student's final average based on his or her homework score, quiz scores, and final exam. Homework counts for 20 percent of the student's final grade. The first two quizzes count for 10 percent each. The second two quizzes count for 15 percent each. The final exam counts for 30 percent of the final grade.
You will also calculate each student's rank in the class. The rank will display which student placed first in terms of his or her overall score, which student placed second, and so forth. Ranks are calculated using the function
RANK (number, ref, [order=0])
where number is the value to be ranked, ref is a reference to the cell range containing the values against which the ranking is done, and order is an optional argument that specifies whether to rank in descending order or ascending order. The default order value is 0 to rank the values in descending order.
Finally, you will create formulas that will look up information on a particular student based on that student's ID so Alice doesn't have to scroll through the complete class roster to find a particular student. Complete the following:
1. Open the Econ workbook located in the Excel3 > Case2 folder included with your Data Files, and then save the workbook as Econ Grades in the location specified by your instructor.
2. In the Documentation sheet, enter your name and the date.
3. Go to the Grade Book worksheet. In cell B5, count the number of student IDs in the range A22:A57.
4. Cells C15 through H15 contain the weights assigned to each assignment, quiz, or exam. In cell J22, calculate the weighted average of the first student's scores by entering a formula that multiplies each score by its corresponding weight and adds the resulting products.
5. Edit the formula in cell J22, changing the references to the weights in cells C15 through H15 from relative references to absolute references.
6. Use AutoFill to copy the formula from cell J22 into the range J23:J57.
7. In cell K22, use the RANK function to calculate how the first student compares to the other students in the class. Use the weighted average from cell j22 for the number argument and the range of weighted averages in the cell range $|$22:$|$57 for the ref argument. You do not need to specify a value for the order argument.
8. Use AutoFill to copy the formula you entered in cell K22 into the range K23:K57.
9. In the range C16:H18, calculate the class average, minimum, and maximum for each of the six grading components (homework, quizzes, and final exam).
10. In cell B8, enter the student ID 14858.
11. Using the VLOOKUP function with an exact match and the student data table in the range A22:K57, retrieve the first name, last name, weighted average, and class rank for student 14858 in the range B9:1312. Use an absolute reference to the lookup table. Note that the first name is found in the third column of the student data table, the last name is found in the second column, the weighted average is found in the tenth column, and the class rank is found in the eleventh column.
12. Brenda Dunford missed the final exam and will be taking a make-up exam. She wants to know what score she would need on the final exam to achieve an overall weighted average of 90. Use Goal Seek to calculate what final exam score Brenda needs to result in a weighted average of 90.
13. Save and close the workbook.
Attachment:- econ_grades_1.xlsx