Part B: Class Statistics For Part B you are to modify the worksheets in Excel that helps you keep track of the marks of a fictitious class. Part B will consist of three sheets: 1. a marks worksheet (similar to Figure B-1) - PT B - Marks 2. a marking scheme worksheet (similar to Figure B-2) PT B - MS 3. class list worksheet (initial data) - PT B-Class List APCO 1P01 - FINAL MARKS Stud Num 648697 688519 642727 617172 Class Average Last Name First Name Alvarez Oliver Armstrong Brian Barnett Francis Byrd Roland 1 A1 19 13 22 20 18.5 AZ 20 24 24 28 24.0 A3 17 16 22 24 19.8 Test 1 68 62 41 65 59.0 Test 2 35 64 52 69 55.0 Project Raw Final 92 74.97 99 79.55 80 i 76.13 77 85.96 1 87.0 79.2 Final 75 80 76 86 79.3 Grade B B A A 1 Fig B-1 APCO 1P01 Marking Scheme Student Francis Barnett Base 25 30 30 70 70 100 Al A2 A3 Test1 Test2 Project Total Weight 15 15 15 15 15 25 22 24 22 52 80 76 B Class Stats min max median avg 75.0 86.0 78.0 79.3 Letter B D F Total Number 2 2 0 0 0 4 100 Grade St Num 642727 Fig B-2 Fig B-3 In the PT B-Class List worksheet, notice that the Assignment 3 column is blank. In Column K, generate random numbers between 18 and 30. Copy just the values into Column E. This simulates the students getting a mark on Assignment 3 out of 30. APCO 1P01 Winter 2020 Assignment 2 For the PT B - MS worksheet, similar to Figure B-2, the section on the right will allow the professor to type in a student number (where it is highlighted in blue) and it will then display above inside the thick box - the student name, their marks for the 6 pieces of work as well as the student's final mark and letter grade. If it is an invalid student number, N/A will appear in all the columns. To do this you will need to use the VLOOKUP function multiple times: =VLOOKUP(lookup value, table array, index_num, (range lookup]) hint - use a value of false for range lookup. The mark and letter grade will be a VLOOKUP of two different columns and use the CONCATENATE function. The class list is sorted by Student Number. Your PT B - Marks worksheet, will sort the class by Last Name and then First Name (since two of the students have the same last name). Formulas: Final Mark calculated by using the student's marks on the first worksheet and the marking scheme on the second worksheet for each piece of work, you need to calculate what it contributes to the final mark and add it to the running total for all pieces of work to get a final mark. o For example - if a student got 45 / 60 on the test, and the test was worth 20% of the final mark, then the test would contribute 45/60*20 = 15 marks toward the final mark the calculation of the raw final mark needs to only include cell references (and not hard coded numbers) . ***** Final Mark round the Raw Final Mark to the closest whole number need to format the cells (or use a round function) Grade assign a letter based on the final mark (>79 = A, >69 = B, >59 = C, >49 = D, else it is an F) use an IF statement (use the nested logic that we discussed in the online videos) . Averages - average of each piece of work, the raw final mark, the final mark and the grade . Class Summary - similar to Fig B-3, put below on your worksheet with the student's marks before grades can be finalized, the chair of the department may wish to know how the class did. To that end, the class's lowest (min), highest (max), and average (both median and mean) grades can be easily calculated. finally, you'll want to know how many As, Bs, Cs, Ds, and Fs were earned by the class. eg - count the As: =COUNTIF(K4:K18,"A") . APCO 1P01 Marking Scheme Student Base 25 Weight 15 15 15 A1 A2 Test1 Test2 Project 30 30 70 15 15 70 100 25 Total 100 Grade St Num 642727 A B C D E F G H. K A1 A2 Test1 Random Test2 56 Project 68 20 29 38 20 28 65 69 77 61 16 24 49 65 1 Student Num 2 609446 3 617172 4 621540 5 632529 6 635410 7 642546 8 642727 25 18 69 40 96 Last Name Fisher Byrd Carter Cruz Morgan Cross Barnett Alvarez Webb Lane 25 25 47 61 66 17 30 55 57 95 22 24 41 52 80 9 First Name Rosemarie Roland Virginia Mable Ernesto Michael Francis Oliver Leland Marshall Henrietta Brian Glen Elaine Pearl Earnest 19 20 68 35 648697 650328 92 21 29 65 69 10 11 79 30 16 20 63 61 657850 666295 12 Norris 23 23 44 38 61 13 688519 13 24 62 64 99 14 697814 Armstrong Huff Castro 20 30 47 44 65 15 699349 21 26 40 66 77 16 699579 1024 20 24 52 58 60 17 703364 21 25 42 44 72 Norris Romero Mccoy Hines 18 706106 Pete 17 29 40 41 79 19 16 25 70 69 77 707669 715797 Alberta Brandy 20 Garner 16 26 38 61 95 21 718993 Gray Leo 15 23 55 30 78 22 B D E F G J K M 1 APCO 1P01 - FINAL MARKS 2 3 4 Stud Num Last Name First Name A1 A2 Test 1 Test 2 Project Raw Final Final Grade 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Class Average 26 27 28 29 30 Number Class Stats min Letter B max median 31 avg D 32 33 34 F Total 35 Part B: Class Statistics For Part B you are to modify the worksheets in Excel that helps you keep track of the marks of a fictitious class. Part B will consist of three sheets: 1. a marks worksheet (similar to Figure B-1) - PT B - Marks 2. a marking scheme worksheet (similar to Figure B-2) PT B - MS 3. class list worksheet (initial data) - PT B-Class List APCO 1P01 - FINAL MARKS Stud Num 648697 688519 642727 617172 Class Average Last Name First Name Alvarez Oliver Armstrong Brian Barnett Francis Byrd Roland 1 A1 19 13 22 20 18.5 AZ 20 24 24 28 24.0 A3 17 16 22 24 19.8 Test 1 68 62 41 65 59.0 Test 2 35 64 52 69 55.0 Project Raw Final 92 74.97 99 79.55 80 i 76.13 77 85.96 1 87.0 79.2 Final 75 80 76 86 79.3 Grade B B A A 1 Fig B-1 APCO 1P01 Marking Scheme Student Francis Barnett Base 25 30 30 70 70 100 Al A2 A3 Test1 Test2 Project Total Weight 15 15 15 15 15 25 22 24 22 52 80 76 B Class Stats min max median avg 75.0 86.0 78.0 79.3 Letter B D F Total Number 2 2 0 0 0 4 100 Grade St Num 642727 Fig B-2 Fig B-3 In the PT B-Class List worksheet, notice that the Assignment 3 column is blank. In Column K, generate random numbers between 18 and 30. Copy just the values into Column E. This simulates the students getting a mark on Assignment 3 out of 30. APCO 1P01 Winter 2020 Assignment 2 For the PT B - MS worksheet, similar to Figure B-2, the section on the right will allow the professor to type in a student number (where it is highlighted in blue) and it will then display above inside the thick box - the student name, their marks for the 6 pieces of work as well as the student's final mark and letter grade. If it is an invalid student number, N/A will appear in all the columns. To do this you will need to use the VLOOKUP function multiple times: =VLOOKUP(lookup value, table array, index_num, (range lookup]) hint - use a value of false for range lookup. The mark and letter grade will be a VLOOKUP of two different columns and use the CONCATENATE function. The class list is sorted by Student Number. Your PT B - Marks worksheet, will sort the class by Last Name and then First Name (since two of the students have the same last name). Formulas: Final Mark calculated by using the student's marks on the first worksheet and the marking scheme on the second worksheet for each piece of work, you need to calculate what it contributes to the final mark and add it to the running total for all pieces of work to get a final mark. o For example - if a student got 45 / 60 on the test, and the test was worth 20% of the final mark, then the test would contribute 45/60*20 = 15 marks toward the final mark the calculation of the raw final mark needs to only include cell references (and not hard coded numbers) . ***** Final Mark round the Raw Final Mark to the closest whole number need to format the cells (or use a round function) Grade assign a letter based on the final mark (>79 = A, >69 = B, >59 = C, >49 = D, else it is an F) use an IF statement (use the nested logic that we discussed in the online videos) . Averages - average of each piece of work, the raw final mark, the final mark and the grade . Class Summary - similar to Fig B-3, put below on your worksheet with the student's marks before grades can be finalized, the chair of the department may wish to know how the class did. To that end, the class's lowest (min), highest (max), and average (both median and mean) grades can be easily calculated. finally, you'll want to know how many As, Bs, Cs, Ds, and Fs were earned by the class. eg - count the As: =COUNTIF(K4:K18,"A") . APCO 1P01 Marking Scheme Student Base 25 Weight 15 15 15 A1 A2 Test1 Test2 Project 30 30 70 15 15 70 100 25 Total 100 Grade St Num 642727 A B C D E F G H. K A1 A2 Test1 Random Test2 56 Project 68 20 29 38 20 28 65 69 77 61 16 24 49 65 1 Student Num 2 609446 3 617172 4 621540 5 632529 6 635410 7 642546 8 642727 25 18 69 40 96 Last Name Fisher Byrd Carter Cruz Morgan Cross Barnett Alvarez Webb Lane 25 25 47 61 66 17 30 55 57 95 22 24 41 52 80 9 First Name Rosemarie Roland Virginia Mable Ernesto Michael Francis Oliver Leland Marshall Henrietta Brian Glen Elaine Pearl Earnest 19 20 68 35 648697 650328 92 21 29 65 69 10 11 79 30 16 20 63 61 657850 666295 12 Norris 23 23 44 38 61 13 688519 13 24 62 64 99 14 697814 Armstrong Huff Castro 20 30 47 44 65 15 699349 21 26 40 66 77 16 699579 1024 20 24 52 58 60 17 703364 21 25 42 44 72 Norris Romero Mccoy Hines 18 706106 Pete 17 29 40 41 79 19 16 25 70 69 77 707669 715797 Alberta Brandy 20 Garner 16 26 38 61 95 21 718993 Gray Leo 15 23 55 30 78 22 B D E F G J K M 1 APCO 1P01 - FINAL MARKS 2 3 4 Stud Num Last Name First Name A1 A2 Test 1 Test 2 Project Raw Final Final Grade 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Class Average 26 27 28 29 30 Number Class Stats min Letter B max median 31 avg D 32 33 34 F Total 35