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

A2: Spreadsheet

Proportion of term grade 10%, Due Tuesday October 18 at 4 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 - H) 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 students 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.

  1. 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. (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%
  1. Augments/modifies the table in the 'cutoffs' worksheet  (Columns 'A' and 'B') so that it can be used with a lookup function (Feature #3) to map term grade points to term letter grades. (This feature is worth 0.3 grade points)
  1. Using the modified table from #2, the letter grade for each student will be determined and shown in  Column K (grades worksheet) using the lookup function (LOOKUP or VLOOKUP). (This feature is worth 0.6 grade points)
  1. Uses the Excel AVERAGE function to calculate into Row 51 ('grades' worksheet) the average grade for: each course component (assignments and exams) and the term grade point, the latter of which was calculated in #1. (This feature is worth 0.4 grade points)
  1. (Repeats #2 & 3 but with a different set of cut-offs). Modifies the second data table in the 'cutoffs' worksheet (specified in Columns 'D' and 'E') 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 a lookup function (LOOKUP() or 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)
  1. 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 D & E. 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)
  1. 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.
  1. Uses a Pivot table to display the average term grade point for the 6 faculties . (This feature is worth 0.3 grade points)
  1. 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)

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

D2L configuration for this course

Submitting your work:

  1. The Word documents must be electronically submitted according using D2L. Make sure that you include the final versions of BOTH DOCUMENTS ( in D2L. If you exclude one or more documents then you will not be awarded any marks for the missing features.

D2L configuration for this course

  • You can (and really should) submit work as many times as you wish before the due date
  • Only your latest submission (what you submitted previously will be overwritten by your latest submission)
  • You can only submit one file per assignment. To submit multiple files then use 'zip' to contain all the documents in one zipped document: [How to use zip in Windows 7]. Do not use other compression utilities otherwise your submission may not be marked.
  1. You are not allowed to work in groups for this class. Copying the work of another student will be regarded as academic misconduct (cheating).  For additional details about what is and is not okay for this class please refer to the [notes on misconduct for this course].

Points to keep in mind:

  1. Due time: All assignments are due at 4 PM on the due dates listed on the course web page.  Late assignments or components of assignments will not be accepted for marking without approval for an extension beforehand. Otherwise what you have submitted in D2L as of the due date is what will be marked.
  2. 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. Nor does handing in the wrong document or version of a document constitute sufficient grounds for extra time).
  3. 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 your submit your assignment with enough time before it comes due for you to do a check).
  4. 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 others assignment solution.
  5. Execution: 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).
  6. Marking: assignments will be marked by TAs and marking is due 1 week after the due date after which the marking will be evaluated by the course instructor. If you have questions about marking after the grades have been released then the first person to ask is the TA. After that if you have questions you can contact your course instructor.