Using If and LookUp Formulas Formatting Grades Input boxes in tan Output boxes i
ID: 3800434 • Letter: U
Question
Using If and LookUp Formulas Formatting Grades Input boxes in tan Output boxes in yellow Given data in blue Answers in red Professor Streterstein is a bit absentminded. His instructor’s grade book is a mess, and he would like your help cleaning it up and making it easier to use. In Professor Streterstein’s course, the maximum possible points a student can earn is 750. The following table displays the grade equivalent to total points for the course. Professor Streterstein's Course Points Total Points Calculated Grade 0 F 450 D 490 C- 535 C 560 B- 600 B 635 A- 675 A Help Professor Streterstein rework his grade book. Perform the following: 1 Add a column in the grade book for final grade next to the total points earned column. 2 Use the VLookup Function to automatically assess final grades based on the total points column. 3 Using the If Function, format the workbook so each student’s grade shows a pass or fail— P for pass, F for fail—based on the total points. Professor Streterstein Course Student name Exam 1 Exam 2 Quizz 1 Quiz 2 Quiz 3 Project 1 Project 2 Project 3 Project 4 Total Points Earned Pass/Fail Achey, Sarah 76 100 38 43.5 50 87 48.5 73.72 86 602.72 Acosta, Angelica 100 77 50 48 38.5 96 37.345 97 66.22 610.065 Albannay, Bader 87 100 43.5 49 50 98 48.5 84.39 86 646.39 Alkelabi, Khalid 8 87 4 2 43.5 4 42.195 7.76 74.82 273.275 Alnusf, Ali 96 78 48 37.5 39 75 37.83 93.12 67.08 571.53 Alo, Juanita 98 100 49 32 50 64 48.5 95.06 86 622.56 AlSabt, Sadon A KH A 75 99 37.5 43.5 49.5 87 48.015 72.75 85.14 597.405 Anderson, Chester 87 98 43.5 48 49 96 47.53 84.39 84.28 637.7 Ballantine, David 96 92 48 22.5 46 45 44.62 93.12 79.12 566.36 Barr, Sandor 64 96 32 26.5 48 53 46.56 62.08 82.56 510.7 Barrett, Sarah 53 75 26.5 33.5 37.5 67 36.375 51.41 64.5 444.785 Behbehani, Mohammad 45 93 22.5 39 46.5 78 45.105 43.65 79.98 492.735 Beier, Susanna 78 8 39 33 4 66 3.88 75.66 6.88 314.42 Berg, Yashika 4 100 2 41.5 50 83 48.5 3.88 86 418.88 Blackett, Christopher 66 4 33 49 2 98 1.94 64.02 3.44 321.4 Cabander, Frida 98 83 49 38.5 41.5 77 40.255 95.06 71.38 593.695 Callaghan, Carolyn 67 87 33.5 44.5 43.5 89 42.195 64.99 74.82 546.505 Ceballos, Manuel 89 96 44.5 28 48 56 46.56 86.33 82.56 576.95 Chen, Yu-Chi 83 100 41.5 42.5 50 85 48.5 80.51 86 617.01 Colombin, Meghan 56 45 28 21.5 22.5 43 21.825 54.32 38.7 330.845 Connelly, Brianna 43 78 21.5 28 39 56 37.83 41.71 67.08 412.12 Cooper, Alexander 77 85 38.5 44 42.5 88 41.225 74.69 73.1 564.015 Corson, Bailey 88 74 44 23 37 46 35.89 85.36 63.64 496.89 Cunningham, Brett 46 72 23 39 36 78 34.92 44.62 61.92 435.46 Dietz, Alex 56 66 28 39 33 78 32.01 54.32 56.76 443.09 Dubose, Fallon 78 98 39 45 49 90 47.53 75.66 84.28 606.47 Elenbaas, Andrew 78 74 39 49.5 37 99 35.89 75.66 63.64 551.69 Feuer, Jacob 90 77 45 11.5 38.5 23 37.345 87.3 66.22 475.865 Flaks, Molly 23 88 11.5 22.5 44 45 42.68 22.31 75.68 374.67 Friend, Jason 99 75 49.5 38 37.5 76 36.375 96.03 64.5 571.905 Grzanowski, Michael 76 89 38 39 44.5 78 43.165 73.72 76.54 557.925 Harkavy, Alexander 78 78 39 28 39 56 37.83 75.66 67.08 498.57 Harris, MacGregor 45 46 22.5 33.5 23 67 22.31 43.65 39.56 342.52 Hartman, Brian 56 65 28 21.5 32.5 43 31.525 54.32 55.9 387.745 High, Alexander 43 76 21.5 49.5 38 99 36.86 41.71 65.36 470.93 Hobbs, Zachary 67 78 33.5 44.5 39 89 37.83 64.99 67.08 520.9 Holobinko, Brittney 99 87 49.5 43.5 43.5 87 42.195 96.03 74.82 622.545 Keely, Sara 89 99 44.5 49 49.5 98 48.015 86.33 85.14 648.485 Kendall, Richard 98 76 49 41 38 82 36.86 95.06 65.36 581.28 Kowalski, Kyle 85 100 42.5 37 50 74 48.5 82.45 86 605.45 Kuo, Cheng-Jung 87 98 43.5 33.5 49 67 47.53 84.39 84.28 594.2 Lenox, Matthew 82 78 41 41.5 39 83 37.83 79.54 67.08 548.95 Markham, Julie 67 64 33.5 44.5 32 89 31.04 64.99 55.04 481.07 Martin, Elizabeth 83 56 41.5 37 28 74 27.16 80.51 48.16 475.33 Martinez, Javier 89 53 44.5 50 26.5 100 25.705 86.33 45.58 520.615 Mashburn, Amy 74 43 37 37 21.5 74 20.855 71.78 36.98 416.115 McCain, Donovan 74 99 37 47 49.5 94 48.015 71.78 85.14 605.435 McCauslin, Megan 100 67 50 50 33.5 100 32.495 97 57.62 587.615 McCormick, Evan 94 87 47 4 43.5 8 42.195 91.18 74.82 491.695 McLaughlin, Dorsey 8 67 4 24 33.5 48 32.495 7.76 57.62 282.375 Mellen, Jonathan 74 100 37 36 50 72 48.5 71.78 86 575.28 Miller, Sara 100 89 50 50 44.5 100 43.165 97 76.54 650.205 Nestor, Brian 48 89 24 37 44.5 74 43.165 46.56 76.54 482.765 O'Connor, Justin 72 100 36 37 50 74 48.5 69.84 86 573.34 O'Donnell, Devon 100 56 50 27 28 54 27.16 97 48.16 487.32 Oliveria, Kyle 74 100 37 44 50 88 48.5 71.78 86 599.28 Ollom, Ross 88 100 44 43.5 50 87 48.5 85.36 86 632.36 Olwig, Thomas 54 43 27 32.5 21.5 65 20.855 52.38 36.98 353.215 Parra, Matthew 65 56 32.5 33.5 28 67 27.16 63.05 48.16 420.37 Phan, Thao 67 78 33.5 44.5 39 89 37.83 64.99 67.08 520.9 Pitto, James 89 90 44.5 43.5 45 87 43.65 86.33 77.4 606.38 Pologar, Trenton 74 100 37 37.5 50 75 48.5 71.78 86 579.78 Popalisky, Leslie 87 100 43.5 50 50 100 48.5 84.39 86 649.39 Reinhard, Kyomi 100 54 50 50 27 100 26.19 97 46.44 550.63 Roberts, Jase 87 23 43.5 27.5 11.5 55 11.155 84.39 19.78 362.825 Rosenthaler, Andrew 55 45 27.5 50 22.5 100 21.825 53.35 38.7 413.875 Rudebusch, Justin 100 67 50 49 33.5 98 32.495 97 57.62 584.615 Runes, Corri 100 65 50 50 32.5 100 31.525 97 55.9 581.925 Saario, Courtney 98 89 49 49.5 44.5 99 43.165 95.06 76.54 643.765 Sabloff, Claire 100 67 50 50 33.5 100 32.495 97 57.62 587.615 Shiff, Sari 100 89 50 50 44.5 100 43.165 97 76.54 650.205 Sides, Stacy 100 87 50 50 43.5 100 42.195 97 74.82 644.515 Silerio, Edgar 99 98 49.5 38.5 49 77 47.53 96.03 84.28 638.84 Simmer, Lindsey 77 82 38.5 28 41 56 39.77 74.69 70.52 507.48 Sims, Andrew 56 83 28 43.5 41.5 87 40.255 54.32 71.38 504.955 Smaiely, Mohammed 87 74 43.5 38.5 37 77 35.89 84.39 63.64 540.92 Smith, Ryan 100 55 50 50 27.5 100 26.675 97 47.3 553.475 Snead, Megan 100 100 50 50 50 100 48.5 97 86 681.5 Spencer, William 75 100 37.5 44.5 50 89 48.5 72.75 86 603.25 Sun, Anna 77 74 38.5 39 37 78 35.89 74.69 63.64 517.72 Talbot, Kevin 89 100 44.5 39 50 78 48.5 86.33 86 621.33 Talley, Katrina 100 98 50 50 49 100 47.53 97 84.28 675.81 Tiedtke, Victor 78 94 39 49.5 47 99 45.59 75.66 80.84 608.59 Toomey, Terrance 100 99 50 50 49.5 100 48.015 97 85.14 678.655 Urfalioglu, Cem 78 100 39 32.5 50 65 48.5 75.66 86 574.66 Vallina, Roberta 100 77 50 50 38.5 100 37.345 97 66.22 616.065 Wang, Barry 100 56 50 50 28 100 27.16 97 48.16 556.32 Wei, Yu-Chen 99 8 49.5 49 4 98 3.88 96.03 6.88 414.29 Wieland, Christopher 100 87 50 50 43.5 100 42.195 97 74.82 644.515 Williams, Maria 65 100 32.5 38 50 76 48.5 63.05 86 559.05 Wilson, Christina 98 48 49 46 24 92 23.28 95.06 41.28 516.62 Wurzer, Michael 92 74 46 46.5 37 93 35.89 89.24 63.64 577.27 Yu, Won Sun 93 88 46.5 4 44 8 42.68 90.21 75.68 492.07 Using If and LookUp Formulas Formatting Grades Input boxes in tan Output boxes in yellow Given data in blue Answers in red Professor Streterstein is a bit absentminded. His instructor’s grade book is a mess, and he would like your help cleaning it up and making it easier to use. In Professor Streterstein’s course, the maximum possible points a student can earn is 750. The following table displays the grade equivalent to total points for the course. Professor Streterstein's Course Points Total Points Calculated Grade 0 F 450 D 490 C- 535 C 560 B- 600 B 635 A- 675 A Help Professor Streterstein rework his grade book. Perform the following: 1 Add a column in the grade book for final grade next to the total points earned column. 2 Use the VLookup Function to automatically assess final grades based on the total points column. 3 Using the If Function, format the workbook so each student’s grade shows a pass or fail— P for pass, F for fail—based on the total points. Professor Streterstein Course Student name Exam 1 Exam 2 Quizz 1 Quiz 2 Quiz 3 Project 1 Project 2 Project 3 Project 4 Total Points Earned Pass/Fail Achey, Sarah 76 100 38 43.5 50 87 48.5 73.72 86 602.72 Acosta, Angelica 100 77 50 48 38.5 96 37.345 97 66.22 610.065 Albannay, Bader 87 100 43.5 49 50 98 48.5 84.39 86 646.39 Alkelabi, Khalid 8 87 4 2 43.5 4 42.195 7.76 74.82 273.275 Alnusf, Ali 96 78 48 37.5 39 75 37.83 93.12 67.08 571.53 Alo, Juanita 98 100 49 32 50 64 48.5 95.06 86 622.56 AlSabt, Sadon A KH A 75 99 37.5 43.5 49.5 87 48.015 72.75 85.14 597.405 Anderson, Chester 87 98 43.5 48 49 96 47.53 84.39 84.28 637.7 Ballantine, David 96 92 48 22.5 46 45 44.62 93.12 79.12 566.36 Barr, Sandor 64 96 32 26.5 48 53 46.56 62.08 82.56 510.7 Barrett, Sarah 53 75 26.5 33.5 37.5 67 36.375 51.41 64.5 444.785 Behbehani, Mohammad 45 93 22.5 39 46.5 78 45.105 43.65 79.98 492.735 Beier, Susanna 78 8 39 33 4 66 3.88 75.66 6.88 314.42 Berg, Yashika 4 100 2 41.5 50 83 48.5 3.88 86 418.88 Blackett, Christopher 66 4 33 49 2 98 1.94 64.02 3.44 321.4 Cabander, Frida 98 83 49 38.5 41.5 77 40.255 95.06 71.38 593.695 Callaghan, Carolyn 67 87 33.5 44.5 43.5 89 42.195 64.99 74.82 546.505 Ceballos, Manuel 89 96 44.5 28 48 56 46.56 86.33 82.56 576.95 Chen, Yu-Chi 83 100 41.5 42.5 50 85 48.5 80.51 86 617.01 Colombin, Meghan 56 45 28 21.5 22.5 43 21.825 54.32 38.7 330.845 Connelly, Brianna 43 78 21.5 28 39 56 37.83 41.71 67.08 412.12 Cooper, Alexander 77 85 38.5 44 42.5 88 41.225 74.69 73.1 564.015 Corson, Bailey 88 74 44 23 37 46 35.89 85.36 63.64 496.89 Cunningham, Brett 46 72 23 39 36 78 34.92 44.62 61.92 435.46 Dietz, Alex 56 66 28 39 33 78 32.01 54.32 56.76 443.09 Dubose, Fallon 78 98 39 45 49 90 47.53 75.66 84.28 606.47 Elenbaas, Andrew 78 74 39 49.5 37 99 35.89 75.66 63.64 551.69 Feuer, Jacob 90 77 45 11.5 38.5 23 37.345 87.3 66.22 475.865 Flaks, Molly 23 88 11.5 22.5 44 45 42.68 22.31 75.68 374.67 Friend, Jason 99 75 49.5 38 37.5 76 36.375 96.03 64.5 571.905 Grzanowski, Michael 76 89 38 39 44.5 78 43.165 73.72 76.54 557.925 Harkavy, Alexander 78 78 39 28 39 56 37.83 75.66 67.08 498.57 Harris, MacGregor 45 46 22.5 33.5 23 67 22.31 43.65 39.56 342.52 Hartman, Brian 56 65 28 21.5 32.5 43 31.525 54.32 55.9 387.745 High, Alexander 43 76 21.5 49.5 38 99 36.86 41.71 65.36 470.93 Hobbs, Zachary 67 78 33.5 44.5 39 89 37.83 64.99 67.08 520.9 Holobinko, Brittney 99 87 49.5 43.5 43.5 87 42.195 96.03 74.82 622.545 Keely, Sara 89 99 44.5 49 49.5 98 48.015 86.33 85.14 648.485 Kendall, Richard 98 76 49 41 38 82 36.86 95.06 65.36 581.28 Kowalski, Kyle 85 100 42.5 37 50 74 48.5 82.45 86 605.45 Kuo, Cheng-Jung 87 98 43.5 33.5 49 67 47.53 84.39 84.28 594.2 Lenox, Matthew 82 78 41 41.5 39 83 37.83 79.54 67.08 548.95 Markham, Julie 67 64 33.5 44.5 32 89 31.04 64.99 55.04 481.07 Martin, Elizabeth 83 56 41.5 37 28 74 27.16 80.51 48.16 475.33 Martinez, Javier 89 53 44.5 50 26.5 100 25.705 86.33 45.58 520.615 Mashburn, Amy 74 43 37 37 21.5 74 20.855 71.78 36.98 416.115 McCain, Donovan 74 99 37 47 49.5 94 48.015 71.78 85.14 605.435 McCauslin, Megan 100 67 50 50 33.5 100 32.495 97 57.62 587.615 McCormick, Evan 94 87 47 4 43.5 8 42.195 91.18 74.82 491.695 McLaughlin, Dorsey 8 67 4 24 33.5 48 32.495 7.76 57.62 282.375 Mellen, Jonathan 74 100 37 36 50 72 48.5 71.78 86 575.28 Miller, Sara 100 89 50 50 44.5 100 43.165 97 76.54 650.205 Nestor, Brian 48 89 24 37 44.5 74 43.165 46.56 76.54 482.765 O'Connor, Justin 72 100 36 37 50 74 48.5 69.84 86 573.34 O'Donnell, Devon 100 56 50 27 28 54 27.16 97 48.16 487.32 Oliveria, Kyle 74 100 37 44 50 88 48.5 71.78 86 599.28 Ollom, Ross 88 100 44 43.5 50 87 48.5 85.36 86 632.36 Olwig, Thomas 54 43 27 32.5 21.5 65 20.855 52.38 36.98 353.215 Parra, Matthew 65 56 32.5 33.5 28 67 27.16 63.05 48.16 420.37 Phan, Thao 67 78 33.5 44.5 39 89 37.83 64.99 67.08 520.9 Pitto, James 89 90 44.5 43.5 45 87 43.65 86.33 77.4 606.38 Pologar, Trenton 74 100 37 37.5 50 75 48.5 71.78 86 579.78 Popalisky, Leslie 87 100 43.5 50 50 100 48.5 84.39 86 649.39 Reinhard, Kyomi 100 54 50 50 27 100 26.19 97 46.44 550.63 Roberts, Jase 87 23 43.5 27.5 11.5 55 11.155 84.39 19.78 362.825 Rosenthaler, Andrew 55 45 27.5 50 22.5 100 21.825 53.35 38.7 413.875 Rudebusch, Justin 100 67 50 49 33.5 98 32.495 97 57.62 584.615 Runes, Corri 100 65 50 50 32.5 100 31.525 97 55.9 581.925 Saario, Courtney 98 89 49 49.5 44.5 99 43.165 95.06 76.54 643.765 Sabloff, Claire 100 67 50 50 33.5 100 32.495 97 57.62 587.615 Shiff, Sari 100 89 50 50 44.5 100 43.165 97 76.54 650.205 Sides, Stacy 100 87 50 50 43.5 100 42.195 97 74.82 644.515 Silerio, Edgar 99 98 49.5 38.5 49 77 47.53 96.03 84.28 638.84 Simmer, Lindsey 77 82 38.5 28 41 56 39.77 74.69 70.52 507.48 Sims, Andrew 56 83 28 43.5 41.5 87 40.255 54.32 71.38 504.955 Smaiely, Mohammed 87 74 43.5 38.5 37 77 35.89 84.39 63.64 540.92 Smith, Ryan 100 55 50 50 27.5 100 26.675 97 47.3 553.475 Snead, Megan 100 100 50 50 50 100 48.5 97 86 681.5 Spencer, William 75 100 37.5 44.5 50 89 48.5 72.75 86 603.25 Sun, Anna 77 74 38.5 39 37 78 35.89 74.69 63.64 517.72 Talbot, Kevin 89 100 44.5 39 50 78 48.5 86.33 86 621.33 Talley, Katrina 100 98 50 50 49 100 47.53 97 84.28 675.81 Tiedtke, Victor 78 94 39 49.5 47 99 45.59 75.66 80.84 608.59 Toomey, Terrance 100 99 50 50 49.5 100 48.015 97 85.14 678.655 Urfalioglu, Cem 78 100 39 32.5 50 65 48.5 75.66 86 574.66 Vallina, Roberta 100 77 50 50 38.5 100 37.345 97 66.22 616.065 Wang, Barry 100 56 50 50 28 100 27.16 97 48.16 556.32 Wei, Yu-Chen 99 8 49.5 49 4 98 3.88 96.03 6.88 414.29 Wieland, Christopher 100 87 50 50 43.5 100 42.195 97 74.82 644.515 Williams, Maria 65 100 32.5 38 50 76 48.5 63.05 86 559.05 Wilson, Christina 98 48 49 46 24 92 23.28 95.06 41.28 516.62 Wurzer, Michael 92 74 46 46.5 37 93 35.89 89.24 63.64 577.27 Yu, Won Sun 93 88 46.5 4 44 8 42.68 90.21 75.68 492.07Explanation / Answer
Please do the following steps:
1) Find out what is column code for Total Point earned column. (It will be like m or n or l or any alphabet on very first row. I'm assuming column code is 'L')
2) Decide what is the pass marks. (I'm assuming 500)
3) Then go to first empty row of the Pass/Fail column. And write =IF(L2>= 500,"P","F") and press ENTER
4) Now you should see the value of that column either 'P' or "F'
5) Just clik on the cell, now you can see a dot on selected cell border. Select and hold on the dot and drag it down to all the columns
6) Pass/Fail value will be caluculated automatically or all the students.
Related Questions
Hire Me For All Your Tutoring Needs
Integrity-first tutoring: clear explanations, guidance, and feedback.
Drop an Email at
drjack9650@gmail.com
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.