Assignment 2: Excel spreadsheet

Course web page: Introduction to problem solving with applications James Tam

A2: Spreadsheet

Proportion of term grade 10%, Tuesday February 14 at 5 PM

For this assignment you are to start with the raw grade data in the: [Initial spreadsheet]. The starting sheet contains the fictional grade information for a CPSC 203 class and includes grade points (which range from 0.0 - 4.0) for all 6 course components (Columns C - I) as well as identifying information for the student (ID and faculty). Blank cells indicate that the student didn't attempt a component while zeros indicate that student attempted a component but did so poorly that no credit is to be awarded. The grades are listed in ascending order of student identification number and this ordering cannot be changed. There are two worksheets: 'grades' and 'cutoffs' the former containing the raw grades for a lecture and the latter the cut-offs for grade scales. You should not change this division (e.g. move the cut-offs information into the grade sheet).

You will start this assignment with a grade point of zero. As you implement the features listed below your grade for this assignment will increase by the amounts specified in the italicized text in the brackets. Unless otherwise specified the row/column references refer to the 'grades' worksheet.

Creates an Excel formula (in "Column J") to calculate weighted term grade point for each student. The value is each component is show below. You may want to look at the first set of notes "Introduction to the course / administrative information" if you don't know how calculate a weighted term grade point. You should not calculate the term grade point by using a pre-defined Excel formula e.g. Sumproduct(), instead you should define the formula by typing all the calculations into Excel yourself. (This feature is worth 0.3 grade points)
Partial A1 proportion of term grade = 5% A2 proportion of term grade = 10% A3 proportion of term grade = 10% A4 proportion of term grade = 10% Partial A5 proportion of term grade = 5% Midterm proportion of term grade = 25% Final exam proportion of term grade = 35%
Augments/modifies the table in the 'cutoffs' worksheet (original information specified in Columns 'A' and 'B') so that it can be used with the VLOOKUP() function (Feature #3) to map term grade points to term letter grades. (This feature is worth 0.3 grade points)
Using the modified table from #2, the letter grade for each student will be determined and shown in Column K (grades worksheet) using VLOOKUP(). (This feature is worth 0.6 grade points)
Uses the Excel AVERAGE() function to calculate into Row 51 ('grades' worksheet) the average grade for: each course component (assignments and exams), term grade point, the latter of which was calculated in #1. (This feature is worth 0.4 grade points)
(Repeats #2 & 3 but with a different set of cut-offs). Modifies the second data table in the 'cutoffs' worksheet (original information specified in Columns 'F' and 'G') and performs a 'lookup' of this data. Similar to Feature #2, for Feature #5 you modify the second table into a form that can be used by another employment of VLOOKUP(). The new cut-offs and the result of the alternate cut-offs (number occurrences of each term letter grade) should show the same information about cut-offs (minimum grade point needed for each letter grade) as the original table but clear labels will be needed to clearly differentiated the two cut-off tables to the viewer. You should build the tables yourself rather than using the automated 'What-If Analysis' option built into Excel (see image to right). (This feature is worth 0.9 grade points)
Uses the COUNTIF() function to count the number of occurrences of each term letter grade with both cut-off scales ('cutoffs' worksheet: first set of cut-offs specified in Column A & B while the second set is specified in Column F & G. The count of the occurrences must be displayed in the 'cutoffs' worksheet (because the two set of grade offs are stored here it's more convenient for the viewer to see the effects of the different cut-offs here as well). (This feature is worth 0.8 grade points)
Uses two 'column charts' (see the icon to the right) to display the number of occurrences of each term letter grade (choice of graph is appropriately chosen and clearly labeled) for both cut-off scales. (This feature is worth 0.4 grade points). There should be a separate column chart for each of the cut-off scales so the reader can clearly distinguish the effect of the two scales.
Uses a Pivot table to display the average term grade point for the 6 faculties . (This feature is worth 0.3 grade points)
Bonus feature: If you are awarded perfect marks with Features #1 - 8 and no style penalties then you can be awarded an assignment grade of 4.0. Completing the bonus feature can allow for a grade point over 4.0. You are to work with a spreadsheet that includes personal contact information for several people. Each row of Column A includes the contact information for a single person (e.g. A1 is all the information for Peter Griffin). Each contact will have an address field, a name field and a title field. Each field will be separated by a colon: <address>:<name>:<title> Each field can consist of zero or more characters. Example data is included in the [unformatted spreadsheet]. Column A ("Examples of data") includes some samples of raw data in this format. Your program should handle any data as long as it follows the previously specified format. However you can assume that the data in column A will never exceed 256 characters in length including the two colons. Since you can rely on the two colons always being included in the raw data then width of data in Column A will range from 2 - 256 characters. Column B ("Title and name") will include: the title (characters after the second semi-colon in the string in Column A) and combine it with the name field (characters between the first and second semi-colon in the string in Column A). The final string in Column B should include a space between title and the name. This process of extracting and recombining substrings must be done using the built in features of Excel and must work with any raw string in this format. Enter the function or combination of functions that you think will properly process the data into the first four rows of Column B. You will receive no credit if your spreadsheet only handles some cases or if you manually enter the data into column B. (This feature is worth 0.3 grade points)

Style and formatting deductions (your minimum grade point is 0.0)

Formula addressing: Inappropriate use of absolute vs. relative formulas will result in a penalty of 0.1 grade points (applied once for all occurrences of when the wrong type of formula was applied and not for each occurrence).
Colors: They are used to visually highlight the highest and lowest term grades (A+ and F). You should use the conditional formatting feature so you don't manually change the font colors and so that colors will automatically update as grades change. Alternatively you can map colors another category of information (e.g. faculty). Penalty for omitting the use of colors, mapping too many colors (count as a rule of thumb a half a dozen distinct colors as max not counting shades of colors) or choosing colors with insufficient background/font contrasts : -0.1 penalty for any occurrence
C.R.A.P:
	Contrast: insufficient contrast between headings and grade information: -0.1 penalty for any occurrence
	Repetition: inconsistencies occur with text in headings or the cells with grade information (or both): alignment, fonts, font effects, font sizes: -0.1 penalty for any occurrence
	Proximity: items that are related are located nearby each other, unrelated items are separate -0.1 penalty for any violation of this principle
Fonts and font effects: The number of fonts and font effects/sizes exceed: 3 (max of 3 types of fonts, max of 3 font effects/sizes): -0.1 penalty either maximum is exceeded

Points to keep in mind:

Due time: All assignments are due at 5 PM on the a listed on the course web page. Late assignments or components of assignments will not be accepted for marking without approval for an extension beforehand. The latest versions of the files that you have submitted in D2L as of the due date is what will be marked.
Extensions may be granted for reasonable cases by the course instructor with the receipt of the appropriate documentation (e.g., a doctor's note). Typical examples of reasonable cases for an extension include: illness or a death in the family. Cases where extensions will not be granted include situations that are typical of student life: having multiple due dates, work commitments etc. Tutorial instructors (TA's) will not be able to provide extension on their own and must receive permission from the course instructor first. (Note: Forgetting to hand your assignment or a component of your assignment in does not constitute a sufficient reason for handing your assignment late).
Method of submission: You are to submit your assignment using D2L [help link]. Make sure that you [check the contents of your submitted files] (e.g., is the file okay or was it corrupted, is it the correct version etc.). It's your responsibility to do this! (Make sure that you submit your assignment with enough time before it comes due for you to do a check).
Collaboration: Assignments must reflect individual work, group work is not allowed in this class nor can you copy the work of others. To avoid problems students should not see each other’s assignment solution.
Execution: documents and programs must work on the machines in the 203 computer lab. It's up to you to test and check that this is the case. Non-functional submissions will receive only partial credit (if any at all).

D2L configuration:

Multiple submissions are possible for each assignment: You can and should submit many times before the due date. D2L will simply overwrite previous submissions with newer ones.
Important! Multiple files can be submitted for each assignment. I am allowing you to submit multiple files for each assignment so you don't have to worry about archiving/compressing multiple files using a utility such as zip. However, this means that TAs will only mark the latest versions of each file submitted via D2L. Even if the version of a document that you want marked has been uploaded into D2L if it isn't the latest version then you will only get marks for the latest version. (It's unfair to have the TAs check versions or to remark assignments because marking is enough work as-is).

Marking:

Assignments will be marked by your tutorial instructor (the "Teaching Assistant" or "TA"). When you have questions about marking this is the first person that you should be directing your questions towards. If you still have question after you have talked to your TA, then you can talk to your course (lecture) instructor.