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