Microsoft Office Tutorials and References

In Depth Information

**Session 2.2**

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:

Apply

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

instructor.