CPT 105
Franklin College
Erich Prisner
CPT 105
Franklin College
Erich Prisner
This is the file a teacher keeps on a college class. Download and save the file into your My Documents folder before you open it.
| 1 | Have a look on the five worksheets: Main, Homework, Quizzes, Exams, and Attendance. |
| 2 | Format the headings on each sheet, as well as the "Total" row. Use background colors or text colors. |
| 3 | On the "Homework" worksheet, display the sums of the homework points for the corresponding students in column J (see #13). Display also the sum for the "Total" row, giving the number of possible homework points. |
| 4 | In column K of the "Homework" worksheet, display the ratio of homework points achieved divided by possible points. Do this first only for rows 4, 5, and 6. (see #10) |
| 5 | Format these values in column K as percentages (see #5) |
| 6 | Try to copy the formula in K6 down to the other values of column K (see #11). What happens? Why? Modify the formula, using absolute referencing, and copy it down again. |
| 7 | In column C of the "Main" worksheet, display these values of column K in the "Homework" worksheet simply be referring to them. Click in cell C4 in the "Main" sheet, type "=", and click in cell K4 on the "Homework" sheet. The corresponding cell number reference will be filled in automatically. Copy this down to the whole column C. |
| 8 | In columns L and M of the "Quizzes" worksheet, also get the total points per student and the percentages. Display these values again in column D of the "Main" worksheet. |
| 9 | In the "Exams" worksheet, we transform these points numbers first into percentages in columns F, G, H, and I. That is, column F should display the percentage score for exam 1, and so on. |
| 10 | Adjust the widths of columns F, G, H, and I on worksheet "Exams". |
| 11 | A different formatting should pop up whenever we a student wrote an F in any of these exams. We use conditional formatting (see #8) |
| 12 | Use the AVERAGE function (see #13) to display the average exam percentage in column J of the "Exams" worksheet. Display these values, as usual, in column E of the "Main" worksheet. |
| 13 | On the "Attendance" worksheet, use the COUNT function to display the number of attended classes in column AD (see #13b). |
| 14 | Display the number of missed classes in column B of the "Main" worksheet. |
| 15 | On the "Main" worksheet, insert a column right after column B. Name it "penalty". |
| 16 | The "penalty" column on the "Main" sheet should display "-10%" if the number of missed classes is larger than 4. Use the IF function (see #13). |
| 17 | The "Total" column (former F, now G) in the "Main" sheet should display the final score. It is the average of the total Homework, Quizzes, and Exams scores, plus the penalty for nonattendance in column C. |
| 18 | |
| 19 |
Erich Prisner, January 2006