CPSC 203: Assignment One (Spreadsheet)

Features of Excel that you must include in this assignment:

• Add your own personal contact information (name and student identification number) to cell A2.

• Change the font of cell 'A1' so that it's bold, 12 point in size.

• Change all the titles along row 3 to a bold font type.

• Change the background color for each cell to white.

• Merge cells A1 - C1 so that entire title ‘CPSC 100: Final exam grades’ is always visible. Make sure that cell A1 is left justified. 

• Modify the spreadsheet so that the label ‘Grades Fall 2007’ appears at the bottom right of each page as a footer.

• 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 39.5 marks, the score on the bonus question is extra to that value).  The percentage should show 2 digits for the fractional portion.

• Use the format cell feature so that Column N (labeled ‘Percentage’ displays a % sign): 1 mark

• 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.

• Use the format cell feature so that Row 72 (average percentage displays a % sign): 1 mark

• Calculate the max score actually received by the top 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')

• Change the spreadsheet so that the headings shown on Row 3 will always appear onscreen (even if the viewer of the sheet scrolls the display down to the last few rows of the sheet): 2 marks

• Save the spreadsheet under the following name 'a1version1'.  Make a copy of the sheet and save it as 'a1version2'.  For version 2 of the spreadsheet make the following changes to the ranges for the grade distributions:

• You are to submit both versions of the spreadsheet when you hand in your assignment.