BUAN 132 , Alternate grade book, Excel Assignment.

661256 Rinsert Format Arrange View Share Window Help Assignmentanenstruction online-te Collaborate inter Talle Cart Text Shape Media Alternate Grade Book the following figure displays a possible layout of your homework one. Open the partially completed version of this worksheet given to you, and then complete the worksheet as follows: a. The test average is computed by dropping the student's lowest score, then giving equal weight to the three remaining tests. Andrea Carrion's test average, for example, is computed by dropping a 40 on test 2 or 3, then taking the average of 60, 40, and 81. You will have to use the SUM, MIN and COUNT functions to meet this requirement b. Students are required to complete a designated number of homework assignments (12 in the figure), then receive a bonus or penalty for every additional or missing homework assignment Andrea Carrion completed 9 homework assignments, rather than 12, and thus has a 6 point penalty (2 points per each missing assignment). Steve Weinstein, however, receives a 3-point bonus (1 point for each extra assignment). The bonus or penalty is added to the test average to determine the semester average. You will have to use if function here. C. The grade for the course is based on the semester average and table of grading criteria according to an HLOOKUP function within the worksheet. Please use the correct formulas for ALL of your calculations d. Format the worksheet in an attractive manner. You do not have to duplicate our formatting exactly, but you are to display all failing grades and homework penalties in red e Process the worksheet a second time to reflect the comment in cell E19. Save the result to a second worksheet (not another file). Please do not replace the first worksheet Add your name to the end of each worksheet Add a custom header with your name, the course you are taking and your instructor's name. Create a custom footer with today's date and the current time Then submit it on Blackboard to your instructor, e mi st - MacBook Air D 90 DD a SO E F& PT F A ! 1 N 23 3 $ 4 % 5 & 7 00* 6 0 0 Q W E 0 R 20 T Y U ** 661256 6 sert Format Arrange View Share Window Help AssignmentOneinstruction-anne-Edad T Inen -Table Chan Text Shad Media Coma Collaborate He Antica Moda Date Page LIN Pac Grade 780 NUT B- B Professor's Grade Book - Alternate Grading Scheme Test Number Benus or Semester Test 1 Test2 Test 3 Test 4 Average of HWS Penalty Average 00 50 40 79 570 75 YO 65 05 800 11 00 80 To 60 800 14 2 820 78 05 17 10 00 00 90 70 BOO 15 3 06 135 65 00 65.0 to 4 010 92 96 96 943 883 00 50 65 30 683 12 0 3 78 70 80 300 12 O 800 70 BO 00 100 300 15 3 930 930 Name 4 Go Andrea 5 Gebu Steve Hart, John MoldotAm Run Roberta Share Car 10 Silverman, Alice 11 mith Adam 12 Surrey, blev 13 Wein, Steve 14 15 Grading Criteria 18 A D B- 93 90 60 D 70 85 B 78 B 10 Home Werk Information Required number of assignments Penalty for each assignment missing Bonus for each additional assignment ANN 29 start 10 MacBook Air 00 DU OD 30 F3 F4 FS 18 F7 FB A * @ 2 # 3 $ 4 % 5 C 9 6 7 8 0