# University of Miami Excel Expressions Assignment

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.