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 = 70 Instructions B с D F LETTER GRADE (COLUMN V) = 60 = 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

Solved: Part I: Excel (60 marks) Steps to Perform: Step 1 2 3 5 6 7 8 Instruct

Home
questions and answers
part i excel 60 marks steps to perform step 1 2 3 5 6 7 8 instructions

Question

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,