It is often the case that a particular value is converted into another value for
ID: 3290086 • Letter: I
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.
THE CELLS THAT NEED TO BE FILLED OUT UTILIZING VLOOKUP IN EXCEL IS THE COLUMN UNDER "ADJUSTED SCORE" AND THE COLUMN UNDER "NEW LETTER GRADE". I NEED THE FORMULA THAT GOES INTO EACH CELL IN ORDER TO ACCOMPLISH THIS. 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 3Explanation / Answer
A
B
C
D
E
1
Student
Grade
Missed Classes
Adjusted Score
New Letter Grade
2
Frank
85
2
85
A
3
Mary
35
6
25
F
4
Elizaveta
98
0
98
A+
5
Gladys
74
0
74
B+
6
Epifania
100
4
95
A+
7
Pierre
54
7
44
D
8
Lupe
87
3
82
B+
9
Eliyahud
83
2
83
B+
10
Hamid
86
3
81
B+
11
12
Missed Classes
Pts Off
13
2
0
14
3
5
15
5
10
16
17
0
F
18
36
D
19
50
C
20
62
B
21
74
B+
22
85
A
23
94
A+
For Adjusted Score column,
in the cell D2, we write “=B2-IFERROR(VLOOKUP(C2,$A$13:$B$15,2,TRUE),0)”, where C2 is the value which is to be matched with the range A13:B15 and second column values are to be used in this range so we write 2. We give the dollar sign in that range so that while dragging the pointer through that column that cell range does not change. We include this error message so that when we get a value less than the first value in the range A13:B15, it automatically accepts 0 as the value.
For New letter grade,
First we had to arrange the grades in the cells A17:B23 in ascending order according to column A.
In cell E2, we write “=VLOOKUP(D2,$A$17:$B$23,2,TRUE)”, where D2 is the value to be matched, A17:B23 gives the range where we want to look up and in this range second column values are to be used. Then we just drag it holding the pointer at the corner of the cell.
A
B
C
D
E
1
Student
Grade
Missed Classes
Adjusted Score
New Letter Grade
2
Frank
85
2
85
A
3
Mary
35
6
25
F
4
Elizaveta
98
0
98
A+
5
Gladys
74
0
74
B+
6
Epifania
100
4
95
A+
7
Pierre
54
7
44
D
8
Lupe
87
3
82
B+
9
Eliyahud
83
2
83
B+
10
Hamid
86
3
81
B+
11
12
Missed Classes
Pts Off
13
2
0
14
3
5
15
5
10
16
17
0
F
18
36
D
19
50
C
20
62
B
21
74
B+
22
85
A
23
94
A+
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.