Topics covered: CELL REFERENCING, VLOOKUP, HLOOKUP IF and COUNTIF The screen shot below displays the layout for a final letter grade calculation based on the grading policy at UCM. You will create a similar worksheet which can calculate the final letter grade for a student based on their scores, Instructions are given below the screenshot 1. The overall percentage cell has the formula based on the grading policy given in table below. 2. Students will use the VLOOKUP formula in the cell that displays the final letter grade 3. The grade legend table can be anywhere in the worksheet. 4. All cells except the ones for entering student scores (orange color in screenshot) are "Protected" or locked in in other words; which means they cannot be edited. 5. Enter names for 10 fictitious students in column A. 6. Enter fictitious scores for all graded components for each student. 7. Hover your mouse on the bottom right corner of the overall percentage, and click on the tiny green square. The formula will be applied copied for as many students there are in the list. 8. Apply the technique in step 7 on final grade column to copy the final grade formula for for all students. 9. Type in the COUNTIF formula for grade A, in the cell below the cell "Number of Grades 10. Copy the formula for all other grades, from B through Fin the cells below, hitting the Enter key each time. The result should display the count of the number of grades for each letter grade from your grading data Graded component Points 35% 399 Exam. Finala Five Quizzes 10% letter grade from your grading data. Graded component Posts Finale.com Five Ques Fivelas Research Paper 90% Total: 100% 11. Create a grade distribution table as demonstrated in the assignment demo video posted. 12. Display the number of students who passed and failed the course using the COUNTIF function 13. Create a table as shown in the figure for Kiddie Toys. Use HLOOKUP and other appropriate formulas in your spreadsheet 14. Use the business rules given below (in blue) to create the table above for Kiddie toys. Kiddie Toys Inc., China Packaging Order Distributor and price Price Unit 101 A 103 54 101 C 1800 104 D 108 E 104 F Business Rules Price Seach 3 sos Discount based on Tol Price fool. Price 1001-01 20234 50025 1000 4.75 2000.00 Packaging Costs Method Sleach 101.500 de bugs 0-100 Paper Bags More than 1000 mencabad Plastic Bags Cardboard 10:23 13 ps Gin Wappies 02:30 Quizzes 100 Exam-1 Final Labs Exam 100 100 35% 35% 80 90 90 90 80 90 60 60 0 0 0 0 0 0 0 0 0 0 0 0 Max. points possible Weightage Student 1 Student 2 Student 3 Student 4 Students Student 6 Student 7 Students Student 9 Student 10 100 10% 100 100 60 60 0 Grade Legend 0 59.9 F 60 69.9 D 70 79.9 C 80 89.9 B 90 100 A Research Overall Grade Paper percentage 100 100 10% 10% 90 89 B 100 100 93 A 70 70 80 60 60 60 D 0 0 0 F 0 0 0 F 0 0 0 F 0 0 0 F 0 0 0 0 0 0 0 0 0 Grade distribution 0 1 1 C D F 1 6 Topics covered: CELL REFERENCING, VLOOKUP. HLOOKUP IF and COUNTIF The screen shot below displays the layout for a final letter grade calculation based on the grading policy at UCM. You will create a similar worksheet which can calculate the final letter grade for a student based on their scores. Instructions are given below the screenshot 1 + 1 3 1. The overall percentage cell has the formula based on the grading policy given in table below. 2. Students will use the VLOOKUP formula in the cell that displays the final letter grade 3. The grade legend table can be anywhere in the worksheet. 4. All cells except the ones for entering student scores (orange color in screenshot) are Protected" or locked in in other words, which means they cannot be edited. 5. Enter names for 10 fictitious students in column A. 6. Enter fictitious scores for all graded components for each student. 7. Hover your mouse on the bottom right corner of the overall percentage, and click on the tiny green square. The formula will be applied/copied for as many students there are in "Protected" or locked in in other words, which means they cannot be edited. 5. Enter names for 10 fictitious students in column A 6. Enter fictitious scores for all graded components for each student. 7. Hover your mouse on the bottom right corner of the overall percentage, and click on the tiny green square. The formula will be applied/copied for as many students there are in the list. wch Overall Grade percentage 100 10% 8. Apply the technique in step 7 on final grade column to copy the final grade formula for for all students. 9. Type in the COUNTIF formula for grade A, in the cell below the cell Number of Grades" 10. Copy the formula for all other grades, from B through F in the cells below, hitting the Enter key each time. The result should display the count of the number of grades for each letter grade from your grading data. Graded component Points Exam-1 Final exam 3596 35% For all students 5 6 9. Type in the COUNTIF formula for grade A, in the cell below the cell "Number of Grades" 10. Copy the formula for all other grades, from B through F in the cells below, hitting the Enter key each time. The result should display the count of the number of grades for each letter grade from your grading data. Points Graded component Exam-1 Final exam Five Quizzes Five labs Research Paper 35% 35% 10% 10% 10% Total: 100% 11. Create a grade distribution table as demonstrated in the assignment demo video posted. 12. Display the number of students who passed and failed the course using the COUNTIF function Topics covered: CELL REFERENCING, VLOOKUP, HLOOKUP IF and COUNTIF The screen shot below displays the layout for a final letter grade calculation based on the grading policy at UCM. You will create a similar worksheet which can calculate the final letter grade for a student based on their scores, Instructions are given below the screenshot 1. The overall percentage cell has the formula based on the grading policy given in table below. 2. Students will use the VLOOKUP formula in the cell that displays the final letter grade 3. The grade legend table can be anywhere in the worksheet. 4. All cells except the ones for entering student scores (orange color in screenshot) are "Protected" or locked in in other words; which means they cannot be edited. 5. Enter names for 10 fictitious students in column A. 6. Enter fictitious scores for all graded components for each student. 7. Hover your mouse on the bottom right corner of the overall percentage, and click on the tiny green square. The formula will be applied copied for as many students there are in the list. 8. Apply the technique in step 7 on final grade column to copy the final grade formula for for all students. 9. Type in the COUNTIF formula for grade A, in the cell below the cell "Number of Grades 10. Copy the formula for all other grades, from B through Fin the cells below, hitting the Enter key each time. The result should display the count of the number of grades for each letter grade from your grading data Graded component Points 35% 399 Exam. Finala Five Quizzes 10% letter grade from your grading data. Graded component Posts Finale.com Five Ques Fivelas Research Paper 90% Total: 100% 11. Create a grade distribution table as demonstrated in the assignment demo video posted. 12. Display the number of students who passed and failed the course using the COUNTIF function 13. Create a table as shown in the figure for Kiddie Toys. Use HLOOKUP and other appropriate formulas in your spreadsheet 14. Use the business rules given below (in blue) to create the table above for Kiddie toys. Kiddie Toys Inc., China Packaging Order Distributor and price Price Unit 101 A 103 54 101 C 1800 104 D 108 E 104 F Business Rules Price Seach 3 sos Discount based on Tol Price fool. Price 1001-01 20234 50025 1000 4.75 2000.00 Packaging Costs Method Sleach 101.500 de bugs 0-100 Paper Bags More than 1000 mencabad Plastic Bags Cardboard 10:23 13 ps Gin Wappies 02:30 Quizzes 100 Exam-1 Final Labs Exam 100 100 35% 35% 80 90 90 90 80 90 60 60 0 0 0 0 0 0 0 0 0 0 0 0 Max. points possible Weightage Student 1 Student 2 Student 3 Student 4 Students Student 6 Student 7 Students Student 9 Student 10 100 10% 100 100 60 60 0 Grade Legend 0 59.9 F 60 69.9 D 70 79.9 C 80 89.9 B 90 100 A Research Overall Grade Paper percentage 100 100 10% 10% 90 89 B 100 100 93 A 70 70 80 60 60 60 D 0 0 0 F 0 0 0 F 0 0 0 F 0 0 0 F 0 0 0 0 0 0 0 0 0 Grade distribution 0 1 1 C D F 1 6 Topics covered: CELL REFERENCING, VLOOKUP. HLOOKUP IF and COUNTIF The screen shot below displays the layout for a final letter grade calculation based on the grading policy at UCM. You will create a similar worksheet which can calculate the final letter grade for a student based on their scores. Instructions are given below the screenshot 1 + 1 3 1. The overall percentage cell has the formula based on the grading policy given in table below. 2. Students will use the VLOOKUP formula in the cell that displays the final letter grade 3. The grade legend table can be anywhere in the worksheet. 4. All cells except the ones for entering student scores (orange color in screenshot) are Protected" or locked in in other words, which means they cannot be edited. 5. Enter names for 10 fictitious students in column A. 6. Enter fictitious scores for all graded components for each student. 7. Hover your mouse on the bottom right corner of the overall percentage, and click on the tiny green square. The formula will be applied/copied for as many students there are in "Protected" or locked in in other words, which means they cannot be edited. 5. Enter names for 10 fictitious students in column A 6. Enter fictitious scores for all graded components for each student. 7. Hover your mouse on the bottom right corner of the overall percentage, and click on the tiny green square. The formula will be applied/copied for as many students there are in the list. wch Overall Grade percentage 100 10% 8. Apply the technique in step 7 on final grade column to copy the final grade formula for for all students. 9. Type in the COUNTIF formula for grade A, in the cell below the cell Number of Grades" 10. Copy the formula for all other grades, from B through F in the cells below, hitting the Enter key each time. The result should display the count of the number of grades for each letter grade from your grading data. Graded component Points Exam-1 Final exam 3596 35% For all students 5 6 9. Type in the COUNTIF formula for grade A, in the cell below the cell "Number of Grades" 10. Copy the formula for all other grades, from B through F in the cells below, hitting the Enter key each time. The result should display the count of the number of grades for each letter grade from your grading data. Points Graded component Exam-1 Final exam Five Quizzes Five labs Research Paper 35% 35% 10% 10% 10% Total: 100% 11. Create a grade distribution table as demonstrated in the assignment demo video posted. 12. Display the number of students who passed and failed the course using the COUNTIF function