Final Prob. 8) It is often the case that a particular value is converted into another value for the purpose of analysis. This is usually done by applying some rule(s) to perform the transformation. For example, a score of 85 might translate into an exam letter grade of B+. We can use an extremely useful concept in Excel to make these transformations: VLOOKUP() and HLOOKUP(). Consider an exam for a group of students where the points achieved on the exam are translated in letter grades in the following way. Each exam is adjusted downward by the number of classes missed according to the table below: Grade Scale Missed Classes ..Pts Off 94-100.....A 0-2 85-93......A 3-4 74-84......B+ 5-above 62-73.......B 50-61....... 36-49.......D 0-35.......F 0 3 8 Use VLOOKUP()s for the conversion of the student grade and the grade adjustment. Place the table for the lookups next to the student data and use the area in green for your calculations. 5 3 B 9 Tables for Lookups Adjusted Score New Letter Grade Missed classes Pts Off Student Dato Student Grade Mis-Clas Frank 2 Mary 81 1 Elizaveta 99 6 Gladys 77 2 Epifania 90 0 Pleme 82 Lupe 100 6 Eliyahud 75 6 Hamid 94 Table Lookup Grade Final Prob. 8) It is often the case that a particular value is converted into another value for the purpose of analysis. This is usually done by applying some rule(s) to perform the transformation. For example, a score of 85 might translate into an exam letter grade of B+. We can use an extremely useful concept in Excel to make these transformations: VLOOKUP() and HLOOKUP(). Consider an exam for a group of students where the points achieved on the exam are translated in letter grades in the following way. Each exam is adjusted downward by the number of classes missed according to the table below: Grade Scale Missed Classes ..Pts Off 94-100.....A 0-2 85-93......A 3-4 74-84......B+ 5-above 62-73.......B 50-61....... 36-49.......D 0-35.......F 0 3 8 Use VLOOKUP()s for the conversion of the student grade and the grade adjustment. Place the table for the lookups next to the student data and use the area in green for your calculations. 5 3 B 9 Tables for Lookups Adjusted Score New Letter Grade Missed classes Pts Off Student Dato Student Grade Mis-Clas Frank 2 Mary 81 1 Elizaveta 99 6 Gladys 77 2 Epifania 90 0 Pleme 82 Lupe 100 6 Eliyahud 75 6 Hamid 94 Table Lookup Grade