CPSC 203: Assignment 1 (spreadsheet)

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

A1: Excel Spreadsheet

Proportion of term grade 10%, Due Friday October 13 at 4 PM

For this assignment start with the raw grade data in the: [Initial spreadsheet]. The spreadsheet consists of 4 tabs/worksheets.

You will refer mostly to the worksheets labeled "Overall term grades" and "Cut offs". The other two sheets "Midterm raw" and "Final exam raw" provide grade points that are used to calculate overall term grades but are also for your own reference allowing to see an actual example of how your actual exam percentage scores will be used to determine your term grade point. These latter two sheets contain all data for the two exams such as: the raw total, percentage equivalent and the grade point equivalent of the percentage. Only the last value (exam grade point) will be used in the calculation of the overall term grades. This is how your actual term grade will be calculated and is used to reinforce the point that grade points and NOT percentages will be used to calculate your actual term grade. There is a reference from the Overall term grades worksheet that accesses the appropriate cell in the exam (midterm and final) worksheets. However, since most of the features in the assignment refer to the first worksheet (Overall term grades) unless otherwise specified you can assume that this is the worksheet being referred to in this assignment.

The main worksheet contains the fictional grade information for a CPSC 203 class and includes grade points (which range from 0.0 - 4.0) for all 5 course components (Columns C - G) as well as identifying information for the student (Student ID and faculty).

Assignment features

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. Note that your mark may be reduced by not following stylistic requirements which you were taught in class. However the lowest grade point that you may be awarded for the assignment is 0.0 (negative grade points won't be awarded).

Creates an Excel formula (in "Column H") to calculate a weighted term grade point for each student. The value is each component is shown below. You may want to look at the first set of notes "Introduction to the course / administrative information" if you don't know how to calculate a weighted term grade point. (This feature is worth 0.3 grade points).
A1 proportion of term grade = 10% A2 proportion of term grade = 10% A3 proportion of term grade = 10% Midterm proportion of term grade = 30% Final exam proportion of term grade = 40%
Augments/modifies the table in the Cut offs worksheet (Columns 'A' and 'B' in this worksheet, information for the proposed cut offs are specified in rows 2- 13) so it can be used with the VLOOKUP function (feature #3) to map term grade points to term letter grades (this feature is worth 0.25 grade points). If you are unsure of what modifications are necessary then review the notes that describe the required inputs/arguments to this lookup function as well as the format of that information.
Using the table defined in #2, the letter grade for each student will be determined using the VLOOKUP function, the letter grade returned from this function will appear in Column I - proposed term letter grade. (This feature is worth 0.6 grade points).
Uses the Excel AVERAGE() function to calculate into Row 51 (Overall term 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.6 grade points)
(Repeats #2 with the alternate cut offs). Modifies the second data table in the Cut offs worksheet (the new set of modified cut offs should appear in Column A & B, Rows 16 - 27) . For this feature and the following one you should build the tables and perform the lookup yourself rather than using the automated 'What-If Analysis' option built into Excel (see image to right). (This feature is worth 0.25 grade points)
Similar to #3, use the VLOOKUP function but refer to the table created using the alternate cut offs specified in #5. The return value of this second lookup function should appear in Column J - alternate term letter grade. (This feature is worth 0.6 grade points).
Use the COUNTIF function to count the number of occurrences of each term letter grade with both cut-off scales (First set of cut offs: A1-B13, second set of cut offs: A15 - B27). from the 'cut offs' worksheet. The count of the occurrences must be displayed in the 'cut offs' 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). Put the number of occurrences for each set of cut-offs in a place that is appropriate - use the "Proximity" principle of "CRAP". (This feature is worth 1.0 grade points).
Uses two 'column charts' (see the icon to the right) to display the number of occurrences of each term letter grade. A separate column chart should be created for the number of occurrences of each letter grade for each of the proposed and alternate cut offs. These two charts should also be displayed in the Cut offs worksheet. (This feature is worth 0.4 grade points = 0.2 GPA x 2 charts).
Completing Features 1 - 8 completely and correctly will result in a maximum grade point of 4.0. Bonus feature (correctly implementing the above features plus Feature #9 will allow students to be awarded a grade point of 4.3. In both cases style penalties may reduce the actual student grade below the maximum.
Modify 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 contains 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. Column A ("Examples of data") includes some samples of raw data in this format. Your program should handle any data that follows the 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 the 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). Note: The final string in Column B should include a space between title and the name. While you can store intermediate results in other columns your final result needs appear in Column B. 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 Row 2 - 4 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 even if that data correctly reformats the text.

Style requirements:

Visual design requirements:

Inappropriate use of absolute vs. relative cell references in 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).

Using colors to highlight information in fonts: Penalty for omitting the use of colors (all fonts black), mapping too many colors or choosing colors with insufficient background/font color contrasts: -0.1 penalty for any occurrence (as was the case with absolute vs. relative cell references). Here's how color can be used in this assignment.

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. Another -0.1 penalty can be applied if conditional formatting is not used to produce the color contrast.

Alternatively you can map colors to the various faculties but don't overuse colors. Again conditional formatting must be used. Either map color to either #1 or #2 (not both).

CRAP:

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: either insufficient or excessive spacing between spreadsheet elements, location of cut-off tables and number of occurrences (Feature #7): -0.1 penalty for any occurrence

Font use: a maximum of 3 different fonts (e.g. Calibri, Times New Roman, Arial) and font effects (normal, bold, italics, underline): -0.1 penalty for exceeding either maximum (or exceeding both).

General design requirements:

Don't enter as data values that can be derived e.g. overall term grade point: -0.1 penalty for any occurrence

Labeling information for clarity: -0.1 penalty for any occurrence

Never enter the same information more than once: -0.1 penalty for any occurrence

Submitting your work:

The document must be electronically submitted using D2L.
D2L configuration for this course
- Multiple submissions are allowed for this assignment: You can (and really should) submit work as many times as you wish before the due date. Due dates are strict, only what is in D2L by the deadline is what will be marked. Other methods of verifying that your work was completed on time (e.g. checking timestamps, emailed files etc.) will NOT be accepted.
- Multiple files can be submitted for this assignment (grading spreadsheet and the formatting spreadsheet). Consequently all versions of your submissions will be retained. However only the latest two versions of your spreadsheets are the ones that will be marked, everything else will be ignored (because it is not fair to your marker to sort through multiple versions of your files).
Do not use compression utilities (such as zip) or archiving utilities (such as tar) otherwise your submission may not be marked
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).

Important points to keep in mind:

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.
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 written 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).
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.
Execution: programs must work on the machines in the 203 computer lab. It's up to you to test and check this is the case. Non-functional submissions will receive only partial credit (if any at all).

Grading sheet for A1:

TAs will download this sheet and each student will get detailed feedback on this sheet (to be uploaded in the D2L Dropbox) about how their grade point was derived: [Marking spreadsheet]. A guide to finding feedback documents in the D2L Dropbox: [Guide]