Biology 221 Daivi Emani teaches biology and life sciences at Milford College in White Plains, New York. She wants to use Excel to track the test scores and calculate final averages for the students in her Biology 221 class. She has already entered the homework, quiz, and final exam scores for 66 students. The overall score is based on weighted average of the individual scores with homework accounting for 10 percent of the final grade, each of three quizzes accounting for 20 percent, and the final exam accounting for 30 percent. To calculate a weighted average you can use the SUMPRODUCT function SUMPRODUCT(array1,array2) where array1 is the range containing the weights assigned to each score and array2 is the range containing the scores themselves. Daivi also wants you to calculate each student’s rank in the class based on the student’s weighted average. Ranks are calculated using the RANK function RANK(number,ref[,order=0]) where number is the value to be ranked, ref is a reference to the range containing the values against which the ranking is done, and order is an optional argument that specifies whether to rank in descending order or ascending order. The default order value is 0 to rank the values in descending order. Finally, you will create formulas that will look up information on a particular student based on that student’s ID so that Daivi doesn’t have to scroll through the complete class roster to find a particular student. Complete the following: 1. Open the Biology workbook located in the Excel3 > Case3 folder, and then save the workbook as Biology Grades in the location specified by your instructor. 2. In the Documentation worksheet, enter your name in cell B3 and the date in cell B4. 3. In the Biology Grades worksheet, in cell B5, calculate the number of students in the class by using the COUNTA function to count up the student IDs in the H column and subtracting 1 from that value (so as to not include cell H2 in the count). 4. In the range B8:F8, enter the weight values 10%, 20%, 20%, 20%, and 30%. 5. In the range B9:F9, calculate the average of the numbers in columns K, L, M, N, and O. 6. In the range B10:F10, calculate the minimum values in the corresponding student score columns.Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203Module 3 Performing Calculations with Formulas and Functions | Excel EX 185 7. In the range B11:F11, use the MEDIAN function to calculate the midpoint of each of the student scores. 8. In the range B12:F12, calculate the maximum values for each of the student scores. 9. In cell P3, use the SUMPRODUCT function to calculate the weighted average of 9. In cell P3, use the SUMPRODUCT function to calculate the weighted average of the scores for the first student in the list. Use an absolute reference to the range B8:F8 for the array1 argument, and use the relative reference to the student scores in the range K3:O3 for the array2 argument. 10. In cell Q3, use the RANK function to calculate the first student’s rank in class. Use 10. In cell Q3, use the RANK function to calculate the first student’s rank in class. Use cell P3 for the number argument and column P for the ref argument. You do not to specify a value for the order argument. 11. Calculate the weighted average and ranks for all of the students by using AutoFill to copy the formulas in the range P3:Q3 to the range P3:Q68. 12. In cell B15, enter the student ID 602-1-99 for Lawrence Fujita. 13. In cell B16, use the VLOOKUP function with the student ID from cell B15 to look up the first name of the student matching that ID. Use the range H:Q as the reference to the lookup table, and retrieve the third column from the table. 14. In the range B17:B24, use lookup functions to retrieve the other data for the student ID entered in cell B15. 15. Test the VLOOKUP function by adding other student IDs in cell B15 to confirm that you can Test the VLOOKUP function by adding other student IDs in cell B15 to confirm that you can T retrieve the record for any student in class based on his or her student ID. 16. Manuel Harmon was not able to take the final exam because of a family crisis. Daivi is scheduling a makeup exam for him. A weighted average of 92.0 will give Manuel an A for the course. Use Goal Seek to determine what grade he would need on the final to get an A for the course. 17. Save and close the workbook.This is requirement, the excel is below.