excel code
(e) You have two more columns called Consistent A and Consistent B or C. Display Yes in Consistent A if the student scored no less than 90 on both exams. Otherwise, display No. Similarly, display Yes in the Consistent B or C column if the student received a score no less than 70 and strictly smaller than 90 on both exams. Otherwise, display No. (5 points)
(f) Count the number of students with Yes in the Consistent A and Consistent B or C columns. Place your results in cells F44 and G44, respectively. (5 points) (g) In column H (i.e., Extreme), return Yes if the students Weighted Total is strictly more than 1.5 Population Standard Deviation away from the mean of Weighted Total; otherwise, return No. For example, if the mean of Weighted Total is 50 and the standard deviation of Weighted Total is 10, then any score >65 or
34 5 6 8 6 00 9 ON 10 11 12 13 14 15 16 17 18 19 34 56 228~ ~ ~ ~ ~ ~ 20 21 22 23 Weight Maximun Student ID 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1020 23 24 25 26 27 28 29 30 31 32 6789 01 333 2345 33 34 35 36 37 38 www. Table 1. Exam Weights Examl 40% 100 Exam1 86 82 89 83 77 51 98 81 87 84 74 87 83 77 68 81 83 85 65 75 78 67 91 74 71 81 78 75 59 95 Exam2 60% 100 Exam2 71 75 90 84 76 50 89 89 92 80 78 80 91 86 76 90 85 86 69 73 69 76 99 73 65 81 76 69 66 100 Weighted Total 77 77.8 89.6 83.6 76.4 50.4 92.6 85.8 90 81.6 76.4 82.8 87.8 82.4 72.8 86.4 84.2 85.6 67.4 73.8 72.6 72.4 95.8 73.4 67.4 81 76.8 71.4 63.2 98 Fi 3 4 5 Weight 6 Maximur 7 Student 8 ID 9 1000 10 1001 11 1002 12 1003 13 1004 14 1005 15 1006 16 1007 17 1008 18 1009 19 1010 20 1011 21 1012 22 1013 23 1014 24 1015 25 1016 26 1017 27 1018 28 1019 29 1020 30 1021 31 1022 32 1023 33 1024 34 1025 35 1026 36 1027 37 1028 38 1029 1030 55339 Table 1. Exam Weights Examl 40% 100 Examl 86 82 89 83 77 51 98 81 87 84 74 87 83 77 68 81 83 85 65 75 78 67 91 74 71 81 78 75 59 95 90 40 Exam2 60% 100 Exam2 71 75 90 84 76 50 89 89 92 80 78 80 91 86 76 90 85 86 69 73 69 76 99 73 65 81 76 69 66 100 93 Weighted Total 77 77.8 89.6 83.6 76.4 50.4 92.6 85.8 90 81.6 76.4 82.8 87.8 82.4 72.8 86.4 84.2 85.6 67.4 73.8 72.6 72.4 95.8 73.4 67.4 81 76.8 71.4 63.2 98 91.8 Final Letter Grade Consistent A Consistent B or C Extreme 34 5 6 8 6 00 9 ON 10 11 12 13 14 15 16 17 18 19 34 56 228~ ~ ~ ~ ~ ~ 20 21 22 23 Weight Maximun Student ID 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1020 23 24 25 26 27 28 29 30 31 32 6789 01 333 2345 33 34 35 36 37 38 www. Table 1. Exam Weights Examl 40% 100 Exam1 86 82 89 83 77 51 98 81 87 84 74 87 83 77 68 81 83 85 65 75 78 67 91 74 71 81 78 75 59 95 Exam2 60% 100 Exam2 71 75 90 84 76 50 89 89 92 80 78 80 91 86 76 90 85 86 69 73 69 76 99 73 65 81 76 69 66 100 Weighted Total 77 77.8 89.6 83.6 76.4 50.4 92.6 85.8 90 81.6 76.4 82.8 87.8 82.4 72.8 86.4 84.2 85.6 67.4 73.8 72.6 72.4 95.8 73.4 67.4 81 76.8 71.4 63.2 98 Fi 3 4 5 Weight 6 Maximur 7 Student 8 ID 9 1000 10 1001 11 1002 12 1003 13 1004 14 1005 15 1006 16 1007 17 1008 18 1009 19 1010 20 1011 21 1012 22 1013 23 1014 24 1015 25 1016 26 1017 27 1018 28 1019 29 1020 30 1021 31 1022 32 1023 33 1024 34 1025 35 1026 36 1027 37 1028 38 1029 1030 55339 Table 1. Exam Weights Examl 40% 100 Examl 86 82 89 83 77 51 98 81 87 84 74 87 83 77 68 81 83 85 65 75 78 67 91 74 71 81 78 75 59 95 90 40 Exam2 60% 100 Exam2 71 75 90 84 76 50 89 89 92 80 78 80 91 86 76 90 85 86 69 73 69 76 99 73 65 81 76 69 66 100 93 Weighted Total 77 77.8 89.6 83.6 76.4 50.4 92.6 85.8 90 81.6 76.4 82.8 87.8 82.4 72.8 86.4 84.2 85.6 67.4 73.8 72.6 72.4 95.8 73.4 67.4 81 76.8 71.4 63.2 98 91.8 Final Letter Grade Consistent A Consistent B or C Extreme
