Apply Your Knowledge Project 18 - Formatting Grades Complete the gradebook using the following guidelines. 1. Put the top row of the gradebook on the top line of the worksheet, and set it so that it does not scroll off the scre 2. Put the columns in the following order: Name, exams, quizzes, projects, total points, final grade, pass/fail. 3. Sort the worksheet by name in alphabetical order 4. Complete the total points column. 5. Highlight all calculated columns in yellow. 6. Put a "named range" called Grades on the worksheet with the numeric grade breakdown (see below). 7. Use the VLOOKUP function and the named range to calculate the final grade for each student. 8. In the Pass/Fail column, use an IF statement to display whether the student passed or failed. Assume a D or ab 9. Space the columns so all text is visible and the gradebook is attractive looking. 10. In the total points column, if the total is enough to earn an A, display the numbers in a green font. Display failin Grade Breakdown: Points 0 450 490 535 560 600 635 675 Grade F D CC BB AA Office.microsoft.com is a good resource for learning the syntax of VLOOKUP and IF VLOOKUP( the-value_you're_looking_for, the_range_you're_looking_in, the_column_of_the_value_you_want_to_r IF( the_logical_test, value_if_true, value_if_false) For the values, use quotes around text edge it does not scroll off the screen. , final grade, pass/fail. down (see below). or failed. Assume a D or above is passing. n a green font. Display failing grade numbers in a red font. Do not change the highlighting. of_the_value_you_want_to_return, exact_or_approximate_match) Professor Streterstein Course Student name Project 3 Exam 1 Quizz 1Project 1Exam 2 Quiz 3 Achey, Sarah 73.72 76 38 87 100 50 Toomey, Terrance 97 100 50 100 99 49.5 Oliveria, Kyle 71.78 74 37 88 100 50 Yu, Won Sun 90.21 93 46.5 8 88 44 Harkavy, Alexander 75.66 78 39 56 78 39 Ceballos, Manuel 86.33 89 44.5 56 96 48 Kuo, Cheng-Jung 84.39 87 43.5 67 98 49 Vallina, Roberta 97 100 50 100 77 38.5 Simmer, Lindsey 74.69 77 38.5 56 82 41 Keely, Sara 86.33 89 44.5 98 99 49.5 Alkelabi, Khalid 7.76 8 4 4 87 43.5 Wei, Yu-Chen 96.03 99 49.5 98 8 4 Spencer, William 72.75 75 37.5 89 100 50 Talbot, Kevin 86.33 89 44.5 78 100 50 Reinhard, Kyomi 97 100 50 100 54 27 Behbehani, Moham 43.65 45 22.5 78 93 46.5 Sides, Stacy 97 100 50 100 87 43.5 Wang, Barry 97 100 50 100 56 28 Elenbaas, Andrew 75.66 78 39 99 74 37 Pologar, Trenton 71.78 74 37 75 100 50 Berg, Yashika 3.88 4 2 83 100 50 Silerio, Edgar 96.03 99 49.5 77 98 49 Connelly, Brianna 41.71 43 21.5 56 78 39 Ballantine, David 93.12 96 48 45 92 46 Martinez, Javier 86.33 89 44.5 100 53 26.5 Anderson, Chester 84.39 87 43.5 96 98 49 Wilson, Christina 95.06 98 49 92 48 24 Ollom, Ross 85.36 88 44 87 100 50 Wurzer, Michael 89.24 92 46 93 74 37 Flaks, Molly 22.31 23 11.5 45 88 44 Urfalioglu, Cem 75.66 78 39 65 100 50 Mellen, Jonathan 71.78 74 37 72 100 50 High, Alexander 41.71 43 21.5 99 76 38 Lenox, Matthew 79.54 82 41 83 78 39 Dubose, Fallon 75.66 78 39 90 98 49 McLaughlin, Dorsey 7.76 8 4 48 67 33.5 Cunningham, Brett 44.62 46 23 78 72 36 Martin, Elizabeth 80.51 83 41.5 74 56 28 Alo, Juanita 95.06 98 49 64 100 50 Blackett, Christophe 64.02 66 33 98 4 2 Cooper, Alexander 74.69 77 38.5 88 85 42.5 O'Connor, Justin 69.84 72 36 74 100 50 Holobinko, Brittney 96.03 99 49.5 87 87 43.5 Sun, Anna 74.69 77 38.5 78 74 37 Barr, Sandor 62.08 64 32 53 96 48 Pitto, James 86.33 89 44.5 87 90 45 AlSabt, Sadon A KH 72.75 75 37.5 87 99 49.5 Rosenthaler, Andre 53.35 55 27.5 100 45 22.5 Runes, Corri 97 100 50 100 65 32.5 Corson, Bailey 85.36 88 44 46 74 37 Quiz 2 Project 2 43.5 48.5 50 48.015 44 48.5 4 42.68 28 37.83 28 46.56 33.5 47.53 50 37.345 28 39.77 49 48.015 2 42.195 49 3.88 44.5 48.5 39 48.5 50 26.19 39 45.105 50 42.195 50 27.16 49.5 35.89 37.5 48.5 41.5 48.5 38.5 47.53 28 37.83 22.5 44.62 50 25.705 48 47.53 46 23.28 43.5 48.5 46.5 35.89 22.5 42.68 32.5 48.5 36 48.5 49.5 36.86 41.5 37.83 45 47.53 24 32.495 39 34.92 37 27.16 32 48.5 49 1.94 44 41.225 37 48.5 43.5 42.195 39 35.89 26.5 46.56 43.5 43.65 43.5 48.015 50 21.825 50 31.525 23 35.89 Wieland, Christophe 97 Tiedtke, Victor 75.66 Cabander, Frida 95.06 O'Donnell, Devon 97 Miller, Sara 97 Parra, Matthew 63.05 Mashburn, Amy 71.78 Beier, Susanna 75.66 Talley, Katrina 97 Colombin, Meghan 54.32 Markham, Julie 64.99 Friend, Jason 96.03 Williams, Maria 63.05 McCauslin, Megan 97 Rudebusch, Justin 97 Alnusf, Ali 93.12 Dietz, Alex 54.32 Kendall, Richard 95.06 Albannay, Bader 84.39 McCain, Donovan 71.78 Roberts, Jase 84.39 Saario, Courtney 95.06 Popalisky, Leslie 84.39 Chen, Yu-Chi 80.51 Hartman, Brian 54.32 Hobbs, Zachary 64.99 Kowalski, Kyle 82.45 Callaghan, Carolyn 64.99 Sims, Andrew 54.32 Acosta, Angelica 97 Olwig, Thomas 52.38 Phan, Thao 64.99 Shiff, Sari 97 Sabloff, Claire 97 Harris, MacGregor 43.65 Smaiely, Mohammed84.39 Nestor, Brian 46.56 Barrett, Sarah 51.41 Smith, Ryan 97 Snead, Megan 97 McCormick, Evan 91.18 Feuer, Jacob 87.3 Grzanowski, Michae 73.72 100 78 98 100 100 65 74 78 100 56 67 99 65 100 100 96 56 98 87 74 87 98 87 83 56 67 85 67 56 100 54 67 100 100 45 87 48 53 100 100 94 90 76 50 39 49 50 50 32.5 37 39 50 28 33.5 49.5 32.5 50 50 48 28 49 43.5 37 43.5 49 43.5 41.5 28 33.5 42.5 33.5 28 50 27 33.5 50 50 22.5 43.5 24 26.5 50 50 47 45 38 100 99 77 54 100 67 74 66 100 43 89 76 76 100 98 75 78 82 98 94 55 99 100 85 43 89 74 89 87 96 65 89 100 100 67 77 74 67 100 100 8 23 78 87 94 83 56 89 56 43 8 98 45 64 75 100 67 67 78 66 76 100 99 23 89 100 100 65 78 100 87 83 77 43 78 89 67 46 74 89 75 55 100 87 77 89 43.5 47 41.5 28 44.5 28 21.5 4 49 22.5 32 37.5 50 33.5 33.5 39 33 38 50 49.5 11.5 44.5 50 50 32.5 39 50 43.5 41.5 38.5 21.5 39 44.5 33.5 23 37 44.5 37.5 27.5 50 43.5 38.5 44.5 50 49.5 38.5 27 50 33.5 37 33 50 21.5 44.5 38 38 50 49 37.5 39 41 49 47 27.5 49.5 50 42.5 21.5 44.5 37 44.5 43.5 48 32.5 44.5 50 50 33.5 38.5 37 33.5 50 50 4 11.5 39 42.195 45.59 40.255 27.16 43.165 27.16 20.855 3.88 47.53 21.825 31.04 36.375 48.5 32.495 32.495 37.83 32.01 36.86 48.5 48.015 11.155 43.165 48.5 48.5 31.525 37.83 48.5 42.195 40.255 37.345 20.855 37.83 43.165 32.495 22.31 35.89 43.165 36.375 26.675 48.5 42.195 37.345 43.165 ProjectTotal 4 Points EarnedFinal Grade 86 85.14 86 75.68 67.08 82.56 84.28 66.22 70.52 85.14 74.82 6.88 86 86 46.44 79.98 74.82 48.16 63.64 86 86 84.28 67.08 79.12 45.58 84.28 41.28 86 63.64 75.68 86 86 65.36 67.08 84.28 57.62 61.92 48.16 86 3.44 73.1 86 74.82 63.64 82.56 77.4 85.14 38.7 55.9 63.64 Pass/Fail 74.82 80.84 71.38 48.16 76.54 48.16 36.98 6.88 84.28 38.7 55.04 64.5 86 57.62 57.62 67.08 56.76 65.36 86 85.14 19.78 76.54 86 86 55.9 67.08 86 74.82 71.38 66.22 36.98 67.08 76.54 57.62 39.56 63.64 76.54 64.5 47.3 86 74.82 66.22 76.54