A2: Excel Spreadsheet

Due dates for all assignments can be found on the [graded components] section of the course webpage.

Unless otherwise specified you must follow assignment requirements as specified (e.g. where it says MUST) otherwise credit will not be awarded for the particular feature. The reverse is true when you see the opposite limitation ("YOU MUST NOT") otherwise you will not be awarded credit.

Note: The computer lab for CPSC 203 is located in SS018 (the "Tri-Labs"). If you find that your particular version of Word (e.g. Office 365, older versions of Office, MAC users) does not include everything needed for a feature then you should to complete that feature in SS018. Alternatively for the remote learning version of the course you can login to a lab computer from your own. [Information link created by UC-IT with modifications by James Tam, if you have questions or problems with the remote login contact UC-IT: www.ucalgary.ca/it]

Data file

• To get credit for this assignment you MUST use the starting data file: [grades]. It should go without saying that you should not modify these grade either. Making changes will only slow down marking. In real life if you change the data in a spreadsheet given to you either by client or your boss it won't be acceptable so it won't be acceptable for this course either.
• There are two worksheets in this spreadsheet.

You will refer mostly to the worksheets labeled "Overall term grades" and "Term GPA Cutoffs". 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 as your final numeric score. As required by the university the numeric score is converted to a standard letter grade.

Most of the features in the assignment refer to the first worksheet (Overall term grades) so unless otherwise specified you can assume that this is the worksheet being referred to in this assignment. This sheet contains the fictional grade information for a CPSC 203 class and includes grade points (which range from 0.0 - 4.0 or 4.3 for some) for course components (Columns C - K) 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 bolded text in the brackets.

1. Use the "Freeze Panes" feature of Excel so the headings on Row 2 are always visible (Feature is worth: 0.1 grade points)

2. Use the Excel AVERAGE() function to calculate into Row 51 the average grade for: each course component (Col C - K), plus the weighted term grade point (Col L), with the latter calculated in Feature #5.  The average should display 2 places of precision for the decimal portion (digits right of the decimal point) of the grade. The formula MUST be specified with relative cell references.  (Feature is worth: 0.1 grade points)

3. Specify in Row 52 the lowest grades in for each of the grading components in Col C - Col K (plus the weighted term grade point in  Col L) using the MIN function. The formulas MUST be specified with relative cell references. (Feature is worth: 0.1 grade points) 
 

4. Specify in Row 53 the highest grades in for each of the grading components in Col C - Col K (plus the weighted term grade point in Col L) using the MAX function. The formulas MUST be specified with relative cell references. (Feature is worth: 0.1 grade points) 

 
Define a new formula in (Column L) 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. The formula in the notes is slightly different because the simplified example doesn't have all the components that you have this term but the idea is similar. Multiply the grade point for each component by it's proportion of the term grade.  Sum all the weighted grade components to yield the term grade point.

• A1 proportion of term grade = 20%
• A2 proportion of term grade = 25%
• A3 proportion of term grade = 25%
• A4 proportion of term grade = 20%
• Book exercise proportion of term grade = 10% (2% for each exercise x 5 exercises)

When calculating the 'weight' of a component your spreadsheet MUST use the lookup table defined in range P4 - Q8. The references to the student grade for each component MUST be relative and the references to the weights in the lookup table MUST be ALL absolute. For instance when calculating the weighted A1 grade for student with the ID number 111 the formula will be specified as: C3*$Q$4. The first part of the formula 'C3' is relative because it refers to student 111's particular A1 grade. $Q$4 refers to the part of the lookup table that specifies the 'weight' of an assignment.  Note also that the second part of the formula ($Q$4) is entirely defined as absolute ('dollar signs') because every student's A1 grade is worth 20% of the term grade so every student's term grade calculation will always lookup the weight from cell Q4). Additional explanations of absolute vs. relative will be provided in lecture in the spreadsheet notes. (Don't just look at the slides, you should attend the lectures and/or review the recorded lectures for the explanations).  If you miss the classes where absolute vs. relative was covered in lecture then should get the in-class notes (notes that students manually take in lecture as opposed to the posted electronic slides) of a classmate because most students find this material quite challenging. The term grade point should display 2 places of precision for the rational portion e.g. 3.52. Finally if you are unclear on how to calculate a weighted term grade there is an example (not exactly the same as your assignment) shown in the introduction to the course/administrative notes. (Feature is worth: 0.9 grade points, 0.1 for each component)

6. Defines a lookup table in the worksheet ("Term GPA Cutoffs") Columns A - C. (Col A specifies the minimum value in each range e.g. for a 'B' grade it is 2.85. Col B specifies the maximum GPA in that range e.g. for an A- it is a value lower than 3.85. Col C specifies the letter grades ('A', 'A-' etc.) This table specifies how various ranges of term grade points (that you calculated previously into the cells of "Column L") will be mapped to a term letter grade.  This lookup table will be used by the VLOOKUP function defined in Feature #7 (next feature of this assignment) and use the [following cut-offs]. Note these are the same cutoffs that will be used to convert your term GPA to letter grade at the end of the semester for this course. The requirements for defining a lookup table was covered in the spreadsheet notes when lookup functions were covered. (Feature is worth: 0.3 grade points: 0.025 for each letter grade x 12 letters)

7. Using the table defined in the previous feature, the letter grade for each student will be determined using the VLOOKUP function, the letter grade returned from this function will appear in Column M: "Term letter"). Similar to Feature #5, you MUST define the function with the absolute and relative cell references precisely as specified here. As shown the spreadsheet notes the VLOOKUP function has 3 required inputs/arguments. The first input refers to the cell which contains the student's term grade point and this input MUST be defined as a relative cell reference. The next input refers to the lookup table you defined in Feature #6 and it must be defined as an absolute cell reference. So for Student 111 the first two inputs of the VLOOKUP will appear as follows L3,$A$2:$C$13. Because the lookup table is in another worksheet, the name of the worksheet ("Term GPA Cutoffs") will appear as part of the second input. IOncluding the worksheet information the second argument will appear as 'Term GPA Cut offs'!$A$2:$C$13. Finally the last input is not a cell reference but refers to the column in the lookup table that will be returned by the function (hint this function is supposed to return a letter grade - if you look at the columns of the lookup table you created in the previous feature this should give you a strong clue as to what value to specify as the third argument).  (Feature is worth 0.6 grade points).

8. Use the COUNTIF function to count the number of occurrences of each term letter grade. The count of the occurrences MUST be displayed in the 'Term GPA Cutoffs' worksheet in Col D. The references to letter grades in Column M ($K$3:$K$50) MUST all be absolute. (This feature is worth 0.3 grade points).

9. Plot the distribution of grades using  a 'Column Chart' to display the number of occurrences of each term letter grade. This chart should be located in the 'Term GPA Cutoffs' worksheet approximately in the area from G1 to N17. The title of the chart MUST be "CPSC 203: winter 2063"  (This feature is worth 0.1 grade points).

10. Using the 'Data Validation' feature of Excel to ensure that users do not enter any grade points outside of the range from 0 - 4.0 (Col C, Col G - K). For Col D - F the valid range of grade points is 0 - 4.3. (Adding this error prevention is worth 0.3 GPA for each of theses cases: 1) GPA range from 0 - 4.0  2) GPA range from 0 - 4.3. Total GPA for both ranges = 0.6) The only cells that this applies to ranges from Row 3 -  Row 50. (The grades for the term grade points are derived rather than entered by the user so setting a valid range in those cells in the standard way won't have any effect. For an example of a more complex data validation rule for a predefined function see the workbook "Computer Science Chop-Suey: Chop-Chop Problems", Step by step exercise #9).

    For the 'Input message'it must have the following information: Title = "Error message before input", Error alert = "Enter a GPA from 0 - 4.0". (0.1 GPA in total for both pieces of information for both GPA ranges, 0.05 for each).

    The 'Error alert' (MUST have the following information: Title = "Error message after input" Input message = "GPA must be in the range 0 - 4.0". (0.1 GPA in total for both pieces of information for both GPA ranges, 0.05 for each).

    The Input message and Error alert for  Col D - F must specify a valid range from 0 - 4.3.

    (The total mark for this feature is worth 0.8 grades points in total).

11. Apply the 'Conditional formatting' feature of Excel to the letter grades in Column M. Students in the spreadsheet who are awarded in Column M a letter of 'A+" will have the cell fill color set as black and the font color set as bolded white. In Excel when you can select the Conditional Formatting case 'Format cells that are EQUAL TO' under the 'with' select 'custom format'. From there you can select the cell 'fill color' and the font color. (This feature is worth 0.4 grade points).

12. In Column N you are have a star '*' appear for students who are 'stars' at writing VBA computer programs. This MUST be done using the AND function (0.1 GPA)  in conjunction with the IF function (0.4 GPA). When a student in the spreadsheet has been awarded a grade point of 4.3 in Column E & Column F that person is deemed "a programming star" and a star will appear in corresponding cell in Column N. If the student doesn't meet these requirements then the cell should be left blank. (This feature is worth 0.5 grade points).

Submitting your work:

Important points to keep in mind:

1. Extensions may be granted for reasonable cases by the course instructor with the receipt of the appropriate documentation (e.g., a completed Statutory declaration form that has been signed by appropriate Deponent, you can get an appointment via the Office of the Registrar). 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. Teaching Assistants (the people working in the 203 lab room) will not be able to provide extension on their own and must receive written permission from the course instructor first. (Note: Forgetting to submit/not properly submitting your assignment or a component of your assignment in does not constitute a sufficient reason for special grading considerations).

2. 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.

3. Execution: programs must work on the machines on campus Windows computers. (For the remote learning semester since access to the lab computers is more challenging: so the requirement is slightly relaxed and your file simple has to work on any Windows computer). 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).

4. Late submissions:

   Submission received:	On time	Hours late : >0 and <=24	Hours late: >24 and <=48	Hours late: >48 hours
   Penalty:	None	-1.5 GPA	-3.0 GPA	No credit

Marking:

• If you have questions about your marking then the first person to talk to is your marker and that will be the person who teaches the tutorial in which you are officially registered. [Tutorial information] If you still have questions after this first step then feel free to contact your course instructor, just let me know that you talked to your TA first.
• 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].
• Marking feedback. You will get a detailed marking sheet for each assignment that contains your TA's feedback. This marking sheet is uploaded in your D2L Dropbox. If you don't know how to access files that have been uploaded into D2L then follow this link [viewing uploaded files in the D2L Dropbox]