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 October 16 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 and 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.

Everyone starts this assignment with a grade point of zero. As you implement the features listed below your grade for this assignment will increase by one or two letter 'steps' e.g. one step, "B+" to "A-". The value of each feature is described in the italicized text in brackets. There's a max of ten letter steps that can be earned to yield a maximum credit of 'A'/4.0 that can be earned for this assignment. Completing the seven regular features will earn those ten letter steps. Completing the seven regular features and the bonus feature will allow a grade of "A+/4.3" to be awarded.

  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. (worth 1 letter step)
 
  • 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 (Columns M - N) so that it can be used with a lookup function (feature #3) to map term grade points to term letter grades. (worth 1 letter step)
  1. Determines term letter grade for each student using a lookup function such as VLOOKUP. (worth 1 letter step)
  1. Uses the Excel AVERAGE function to calculate the average grade for each student's course component and term grade point (worth 1 letter step)
  1. Performs a "what-if" analysis to determine the effect of using alternate cut-offs for term letter grades (you need to create whatever table and perform whatever calculations so that Excel can determine the number of occurrences of each letter grade with the new cutoff scale). The new cut-offs and the result of the alternate cutoffs (number occurrences of each term letter grade) should show the same information as the original set of cutoffs (use labels so the two cutoffs can be differentiated) (worth 2 letter steps).

Min A+ = 4.15; Min A = 3.85, Min A- = 3.5, Min B+ = 3.15, Min B = 2.85, Min B- = 2.5, Min C+ = 2.15, Min C = 1.85, Min C- = 1.5, Min D+ = 1.15,, Min D = 0.7, Less than 0.7 = F (e.g. GPA 3.85 to less than 4.15 is an "A" grade)

  1. Uses the COUNTIF function to count the number of occurrences of each term letter grade with both cut-off scales (worth 2 steps)
  1. Uses a bar graph 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 (worth 1 letter step)
  1. Uses a Pivot table to display the average term grade points for the 6 faculties (worth 1 letter step)

Bonus feature (correctly implementing the above 7 features plus feature #8 will allow students to be awarded an "A+" grade. Completing the bonus feature will increase your grade by a full letter grade e.g., "A" to "A+". In order to get credit for the bonus you must fulfill the requirements for features for Features 1 - 7 AND completely and correctly complete Feature number 8.

  1. You are to work with a spreadsheet that includes personal contact information for several people. Each row in the first column includes the contact information for a single person. 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 extract 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: You can go down letter steps for the following cases (your minimum grade is "F" however, negative grade points aren't possible).

Submitting your work:

  1. The Excel document must be electronically submitted according to the assignment submission requirements using D2L.
  2. 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].