It is often the case that a particular value is converted into another value for

Question

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 a letter grade of B+. We can use an extremely useful concept in Excel to make these transformations: VLOOKUP() and HLOOKUP().

Consider an assignment for a group of students where the points achieved on the assignment are translated in letter grades in the following way. Each assignment 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 0

85-93.......A-          3-4                    5

74-84.......B+       5-above             10

62-73.......B

50-61.......C

36-49.......D

0-35.........F   

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.

PLEASE LIST ALL STEPS TO COMPLETING THIS ASSIGNMENT IN EXCEL. NEED TO KNOW WHAT GOES INTO CELLS, etc. THANK YOU!!!

Student Data

Table for Lookups

Table Lookup Grade

Student Grade Missed Classes Adjusted Score New Letter Grade Frank 85 2 Mary 35 6 Elizaveta 98 0 Gladys 74 0 Epifania 100 4 Pierre 54 7 Lupe 87 3 Eliyahud 83 2 Hamid 86 3

Explanation / Answer

From the Excel we use Vlookup function to find out the grades of many observations of Student Scores of different subjects very easily.

First we know that how to use Vlookup function is as follows

You want to calculate grades using Vlookup() function,you just need a given table that acts as a key with scores on left and Grades on Right.

and that table must be sorted in asending order , and Vlookup() must be configured to do an approximate match.

The syntax of VLOOKUP() function is as follows,

SYN: VLOOKUP(lookup_value,Table_Array,Column_index_number,Range_lookup)

          where

         Lookup_value : It represents the required value in the table to convert as a grade.

         Table_Array: this is the Range in Excel work sheet which user wants to search the lookup values. and Vlookup function is not a case sensitive.

         Column_index_number: it is a reqiured parameter of Vlookup function in excel,We need to mention column index number in the Vlookup formula to tell Excel from which column of the given array to be picked a row value of the lookup value.

        Range_lookup: it means to write TRUE or FALSE,because if you mentioned True then it displace the approximated value,or if you write FALSE then it displace the Exact value or character.

From the given data we clculate the Grades of different students as follows

TABLE LOOKUP GRADE:

Since Grade F = Score is 0 to 35

          Grade D = Score is in between 36 to 49

          Grade C = Score is in between 50 to 61

          Grade B = Score is in between 62 to 73

          Grade B+ = Score is in between 74 to 84

          Grade A- = Score is in between 85 to 93

          Grade A = Score is in between 94 and more.

Hence the required data is given by,

First in excel we enter =Vlookup() in the cell by using the above syntax then we can get the grade of the reqiurd value,and drag it untill to reach the end of the cells to wanting grades.then it is given by

--------------------------------------------------- x -------------------------------------------

Table for Lookups Missed Classes Points Off 0-2 0 4-Mar 5 5 above 10

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.