Question by Jié Déshī: In MS Excel, how to use a transmutation table to display equivalent scores in the cells?
Godbless!… I’m a college instructor… I am utilizing MS Excel in my grading sheet… I want to use a formula to display equivalent scores in the cells by having a formula that uses a transmutation table… I have typed the row scores already… What formula should I use?… Help… Tnx a lot, and Godbless… ;-D
Best answer:
Answer by DL
what the hell is a transmutation table?
I’m pretty sure I can fix your problem, but I won’t know until I get an example file. Send me an email if you’re interested and I’ll send you my real email address where you can attach a file.
Know better? Leave your own answer in the comments!
computer programming service,web site design,homework help
custome website design etc, at
http://cynerge.net/
There are a couple of ways you could do this depending on the end goal and the format of the “Transmutation” table.
The Transmutation table is more generally referred to in Excel as a Look-up Table. If your table has letter grades in one column and numeric equivalents in a second column e.g.
G…H
——-
A+ 4.0
A 3.9
A- 3.5
B+ 3.2
B 2.9
B- 2.5
C+ 2.2
C 1.9
C- 1.5
D+ 1.2
D 0.9
D- 0.7
F 0.0
And you have a Letter Grade in cell A1
Put this in B1 to get the numeric equivalent of the Letter grade
=VLOOKUP(A1,G1:H13,2,0)
or this in B1
=INDEX(H1:H12,MATCH(A1,G1:G13,0))
The formulas above search for an exact match in column G.
If you are doing a numeric look-up and the look-up table has numeric cutoffs for each Grade e.g.
G…H
——-
0 F
50 D-
53 D
57 D+
60 C-
63 C
67 C+
70 B-
73 B
77 B+
80 A-
86 A
93 A+
(It’s important to list them from Lowest to Highest in this case)
If A1 had the value 76
Put this in B1
=LOOKUP(A1, G1:G13, H1:H13)
…this formula will return the letter grade “B” for the numeric grade 76 from cell A1
In this example, you could also put the look-up table within the formula…
=LOOKUP(A1, {0, 50, 53, 57, 60, 63, 67, 70, 73, 77, 80, 86, 93}, {“F”, “D-“, “D”, “D+”, “C-“, “C”, “C+”, “B-“, “B”, “B+”, “A-“, “A”, “A+”})
.