Part I: Excel (60 marks)
Steps to Perform:
Step
1
2
3
5
6
7
8
Instructions
Start Excel. Download and open the file named grades_excel_for_al_xlsx.
Rename the given worksheet as grades_Winter2022.
Enter a heading for this document titled 'Student Grades for CIS1201 Winter 2022 in
G2; merge G2 with cells H2, 12, J2, K2, and L2, such that the heading appears in the
center of G2:L2. Increase its font to 16, change the font to Arial, and bold it.
Note that A5:A104 are empty. Use the Fill Series feature to enter student ids beginning with
1111111 for A5, increase by 1 such that A104 gets 1111210.
Note that the grades stored in the given sheet have an inconsistent format. Format all grades
in the range B5:S104 with two decimal places and apply bold and center. The entire range of
values should look the same after this step.
Add a new column between Chapter11 and Final Exam and enter its description as
Textbook assessment. This column will store the average grade a student achieved in the
textbook chapter activities. Note that there are 11 chapters (columns H to R) - you must use a
function that drops the lowest grade and uses the top 10 grades from these 11 columns to find
the average grade and store it in Textbook assessment column. Format all grades in this
column with two decimal places. The average must be in the percentage number format.
Note that columns C and D store student's lab exam I and lab exam II grades. The max grade
for these 2 columns is 10 but the instructor would like all grades to be stored in percentage
(out of 100). Enter 10 in column C2; Use the copy-and-paste-special method to update the
existing grades in columns C and D to be stored out of a max of 100 now (e.g. 9 should be
updated 90). Reapply any formatting if it is removed, so the entire table is consistent.
Create column U and call it 'Overall Grade'. Use a formula as given below to calculate the
overall grade in a percentage number format (same as Step 6):
Overall Grade = 10% of Lab Attendance (Column B) +
10% of Lab Exam I (Column C) +
10% of Lab Exam II (Column D) +
25% of (Assignment1+Assignment2+Assignment3) +
10% of Textbook Assessment +
35% of Final Exam / 100
Points
Possible
0
2
2
2
2
6
5/nStep
9
10
11
12
13
14
15
16
17
Create column V and call it "Letter Grade'. Use a formula as given below to calculate the letter
grade:
OVERALL GRADE
(COLUMN U)
>= 80
< 80 AND >= 70
Instructions
B
с
D
F
LETTER GRADE
(COLUMN V)
<70 AND >= 60
< 60 AND >= 50
<50
Hint: Use function IF in a nested way.
Create a new worksheet and name it Overall statistics_W2022. Using cell referencing, copy
the values in columns with Id. Overall Grade and Letter Grade from grades Winter2022 to
this worksheet.
ABCDE
Count
52
23
Create a heading for this worksheet titled "Overall Statistics of Student Grades CIS1201
Winter 2022". This heading must have the same format as the heading from Step 3.
F
In worksheet Overall statistics_W2022, create a column, name it Grades, and fill it with
letters A, B, C, D, and F. Create another column, name it Count and fill it with the total
number of students that scored grades A, B, C, D or F. You must get the following count
Grades
A
11
8
6
Hint: Use function COUNTIF on C5:C104 of this worksheet.
Draw a 3D-column graph using Grades as the X-axis and Count as the Y-axis. Title the graph
as "Distribution of Grades - CIS1201 Winter2022"; title the X-axis as "Letter grades" and Y-
axis as "Total Number of Students".
In Rows 106, 107, 108 and 109, use functions to calculate and display the Mean, Median, Max
value and Min Value of the overall grades in Column B of worksheet
Overall statistics_W2022.
Create a third worksheet and name it grades_Sorted_FinalExam. Add columns id, Final Exam,
Overall Grade and Letter Grade. Sort this worksheet on the Final Exam grades, in order of
largest to smallest.
On the grades_Sorted_FinalExam sheet, insert the Draft.png picture on the Sheet
Background. This can be downloaded from the assignment page on Courselink.
Ensure that the worksheets are in the following order: grades_Winter2022,
Overall statistics_W2022, grades_Sorted_FinalExam. Make sure that the workbook is
saved as an Excel workbook, not a macro-enabled workbook. Close Excel, and then
submit the workbook as directed.
Total Points
Points
Possible
10
3
2
6
6
4
4
2
0
60
