Microsoft Office Tutorials and References
In Depth Information
22. Change the loan from a 15-year loan to a 20-year loan. What effect does this have on
the monthly loan payment and the conclusion about the affordability of the loan?
Print the contents of the revised Loan Analysis worksheet.
23. Save your changes to the workbook, and then close it.
Case Problem 1
Data File needed for this Case Problem: Chem1.xls
Chemistry 303 Karen Raul is a professor of chemistry at MidWest University. She has
started using Excel to calculate the final grades for students in her Chemistry 303 course.
The final score is a weighted total of the scores given for a student’s homework, lab work,
exams, and final exam. Karen needs your help in creating the formulas to calculate the
final score and to calculate the class averages. One way of calculating a weighted sum is
to multiply each value by the corresponding weight. For example, consider the following
sample exam scores:
Using what you have
learned in Session 2.1,
create a grading sheet
for a chemistry class.
• Exam 1 = 84
• Exam 2 = 80
• Exam 3 = 83
• Final exam = 72
If the first three exams are each given a weight of 20% and the final exam is weighted
40%, then the weighted sum is:
84*0.2 + 80*0.2 + 83*0.2 + 72*0.4 = 78.2
Karen has entered the weights of the exam scores for each of her students. She needs you
to calculate the weighted score as well as some statistics for each exam, including the aver-
age score, the maximum and minimum score, and the range of scores.
To complete this task:
1. Open the Chem1 workbook located in the Tutorial.02\Cases folder included with
your Data Files, and then save the workbook as Chem2 in the same folder.
2. In the Documentation sheet, enter your name in cell B3 and then enter the current
date in cell B4 using the TODAY() function. Increase the width of column B to display
the date, if necessary.
3. Switch to the Grades worksheet and, in cell F7, enter a formula using cell references
to calculate the weighted sum of the four exam scores using the exam values in cells
B7, C7, D7, and E7 and the weights in cells B4, C4, D4, and E4.
4. In the formula in cell F7, change the cell references for cells B4, C4, D4, and E4 from
relative to absolute references.
5. Use Auto Fill to copy the formula in cell F7 into the range F7:F42.
6. In cell B44, enter a formula using the COUNT function to count the Exam 1 scores in
range B7:B42. In cell B45, enter a formula using the AVERAGE function to calculate
the average of the Exam 1 scores in range B7:B42. In cell B46, enter a formula using
the MAX function to calculate the maximum Exam 1 scores in the same range. In cell
B47, enter a formula using the MIN function to calculate the minimum Exam 1 score
for the same. In cell B48, enter a formula to calculate the range of the Exam 1 scores
(equal to the maximum score minus the minimum score).
7. Copy and paste the formulas in range B44:B48 into the range C44:F48 to calculate the
same statistics for the other three exams and the weighted total of all the exams.
8. Print the contents of the Grades sheet in landscape orientation.
9. Save your changes, and then submit the completed workbook and printout to your