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 Friday Feb 26 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.

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 I") 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%
  • Midterm proportion of term grade = 25%
  • Final exam proportion of term grade = 40%
  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 table defined in #2, the letter grade for each student will be determined using the lookup function, letter grade is shown in Column J: LOOKUP or VLOOKUP using a lookup table (this feature is worth 0.6 grade points)
  1. Uses the Excel AVERAGE function to calculate into Row 51 the average grade for: each course component 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 cutoffs). Modifies the second data table in the 'cutoffs' worksheet and performs a 'lookup' of this data. Similar to #2, for #5 you modify the second table specified in Columns 'D' and 'E' 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 cutoffs (number occurrences of each term letter grade) should show the same information about cutoffs  (minimum grade point needed for each letter grade) as the original table but clear labels will be needed to clearly differentiated the two cutoff 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 cutoffs 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 cutoffs here as well). (this feature is worth 0.8 grade points)
  1. Uses a 'column chart' (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)
  1. Uses a Pivot table to display the average term grade points for the 6 faculties using the original cutoff points. (this feature is worth 0.3 grade points)

Bonus feature (correctly implementing the above features plus feature #9 will allow students to be awarded a grade point of 4.3. In order to get credit for the bonus you must fulfill the requirements for features for Features 1 - 8 AND completely and correctly complete Feature number 9 with no style mark deduction.

  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.

 

 

Style and formatting deductions (your minimum grade point is 0.0 however, negative grade points aren't possible).

When using a formula absolute vs. relative cell references are used when appropriate to the situation. Inappropriate use of absolute vs. relative formulas will result in a penalty of 0.3 grade points (applied once for all occurrences of when the wrong type of formula was applied and not for each occurrence).

Submitting your work:

  1. The document must be electronically submitted according to the assignment submission requirements using D2L.

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. If you wish to submit multiple files (not really needed for this assignment) then use 'zip' to contain all the documents in one 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. 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).
  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).