For this assignment you are to use the grade information
from the partially completed MS-Excel spreadsheet that
contains the grade information for a final exam in a fictitious Computer Science
lecture. The fully completed assignment will perform a statistical
analysis of the raw grades using the built in functions (AVERAGE,
MAX, VLOOKUP etc.) and display the grades a graphical form. In
addition, you are to perform a 'what if' analysis of the grade distribution by
using two different percentage ranges for the various letter grades - how many
students will be granted a particular letter grade using each distribution. There
were 67 students in the class. The final exam consisted of 10 questions
worth a total of 37 marks. In addition students could complete a bonus
question worth 1 mark that was added onto the 37 marks (i.e., students could
conceivable receive a score of 38 out of 37).
Features of Excel that you must include in this assignment:
|
| |
- Change the font of cell 'A1' so that it's bold, 12 point in size.
|
| |
- Change the titles (along row 3) to a bold font type.
|
| |
- Change the background color for each cell to white.
|
| |
- Use the 'SUM' function to calculate
in column 'M' the raw score for each student (adds together the score that
they received for all ten examination questions plus the bonus question).
The raw score should show 2 digits for the fractional portion.
|
| |
- Calculate in column 'N' the percentage score for each student (note
that the exam is out of 37 marks, the score on the bonus question is extra
to that value). The percentage should show 2 digits for the
fractional portion.
|
| |
- Use the 'VLOOKUP' function to
determine the letter grade of each student for column 'O'. The
percentage ranges for each letter are to be determined by the values in
the cells S4 to T16.
|
| |
- Determine the number of students that received each letter grade using
the 'COUNTIF' function and show these
values in column 'U'.
|
| |
- Calculate the raw average score (two fractional digits) for each
question into row 71 using the 'AVERAGE'
function.
|
| |
- Calculate the average percentage for each question (two fractional
digits) for each question into row 72. (There's a number of
different ways that you can do this but make sure that Excel actually
calculates this value using some sort of formula or built in function.
You shouldn't, for example, calculate the value by hand and then manually
enter the percentage into the spreadsheet by hand. The percentages
should show two fractional digits.
|
| |
- Calculate the max score actually received by a student into row 73
using the 'MAX' function. For
example, if the question was out of 3 marks and everyone received a 0 or 1
on the question except for one student who received a grade of 2 marks then
the maximum value actually awarded to a student was 2 points (and
not the theoretical maximum of 3 marks).
|
| |
- Produce a bar graph that shows the number of students who received
each of the possible letter grades (A+ to F). The graph should be
located somewhere under the table that maps letter grades to particular percent
ranges (roughly within column 'R' and column 'S')
|
| |
- Save the spreadsheet under the following name 'a2version1'. Make
a copy of the sheet and save it as 'a2version2'. For version 2 of
the spreadsheet make the following changes to the ranges for the grade
distributions:
|
| |
|
Letter |
Percentage range |
| |
|
A+ |
100% (perfect score or higher) |
| |
|
A |
90 - 99% |
| |
|
A- |
85 - 89% |
| |
|
B+ |
80 - 84% |
| |
|
B |
75 - 79% |
| |
|
B- |
70 - 74% |
| |
|
C+ |
65 - 69% |
| |
|
C |
60 - 64% |
| |
|
C- |
55 - 59% |
| |
|
D+ |
50 - 54% |
| |
|
D |
45 - 49% |
| |
|
D- |
40 - 44% |
| |
|
F |
Less than 40% |
| |
- You are to submit both versions of the spreadsheet when you hand in
your assignment.
|