[27 marks] Submit the answer to this question as a .xlsx file. Imagine that you are the Teaching Assistant for a fictional COMP 1042 course. The professor wants to create a spreadsheet to record the marks for the students in the class. As the TA it is your responsibility to design, implement, and test this spreadsheet.

Import the grades data from the sampleMarks.txt file containing a list of students and the raw data for the marks. The file is tab delimited, (i.e., the items are separated by tabs). Be sure to "treat consecutive delimiters as one" or your data may not import properly.

The marks for the course consist of 5 assignments, 2 midterms, and 1 final exam. The following is the marking scheme for each:

• Assignment 1 is out of 35 marks
• Assignment 2 is out of 40 marks
• Assignment 3 is out of 25 marks
• Assignment 4 is out of 65 marks
• Assignment 5 is out of 50 marks
• The first midterm is out of 40 marks
• The second midterm is out of 35 marks
• The final exam is out of 120 marks

Each item in the course is worth a different weight of the final grade:

• The assignments are worth 30% of the final mark
• The tests are worth 30% of the final mark (15% each)
• The final exam is worth 40% of the final mark

Note: eventhough each assignment is marked out of a different total amount they are all worth the same weight. However, the worst assignment for each student will be dropped from the final mark, and the remaining 4 will make up the 30% (7.5% each).

To calculate the final marks you will need to compute the weighted sum of all of the items, given the weighting criteria. final = (item1 * weight1) + (item2 * weight2) ..., where each item here is the student's mark (grade / maximum mark), for the assignment or test.

Create a spreadsheet that has a title and headers indicating what each column represents and a row for each student in the class. There should be a column for each item in the input data. 

The spreadsheet should calculate the following (note, you may require extra columns for intermediate calculations):

• A sub-final grade for each student using the above weighting criteria.
• Another sub-final grade for each student with the midterm weight at 10% each, and the final exam worth 50%.
• The final grade for each student using the best of the two numbers above. This should be formatted as a number with two decimal places (e.g.: 90.70).

Additionally, your sheet should show:

• The class average for each assignment, test, and final exam.
• The average final grade for the class.
• The highest and lowest grades for each assignment, test, and final.
• The highest, and lowest final marks in the class.

Next, you should also have a column indicating (by a "above" or "below") whether each student's final grade is above or below the average grade of the class. (Use "above" if the grade is equal to the average).

Next, there should be a column with a comment using the following marking scheme:

• 80 and above: "Excellent"
• Between 70 and 79.99: "Very good"
• Between 60 and 69.99: "O.K."
• Below 60: "Not great"

Next, use conditional formatting to highlight the entire row of any students that failed the course (final final mark < 50%) with a red text. With a second formatting rule, highlight any students that will receive an A or better, with green background. Note: do not use manual formatting for this. The TA's will look for the conditional formatting rules to ensure your sheet was built correctly.

Finally, your worksheet should be sorted alphabetically by student name. Be sure that your spreadsheet is easily readable and well formatted.

Your spreadsheet will be evaluated as follows:

• Total: 27 marks
• Importing marks file: 3 marks
• Spreadsheet appearance and formatting: 3 marks
• Correct final,final grades: 6 marks
• Correct comment column: 5 marks
• Correct averages and maximums: 3 marks
• Conditional formatting used to highlight failed students: 5 marks
• Correctly sorted table: 2 marks

TXT FILE ATTACHED

Attachments:

• 1515230836-sampleMarks.txt