Changing Student Marks...

[Sabotage1945]

Hello everyone,

I'm sort of new to Excel, and I've been asked to create some way
(formula) or (script) for me to use in order to 'read' and 'place' an
equivalant numerical number to an alphabetical list. Here is an
example:

I'm creating a student report list. The students have marks listed as
'A+', 'A', 'A-', 'B+', 'B', etc. I would like a quick an easy way of
converting these in NEW column in a numeric format. ie: 'A+' = '10'
and down to 'F' = '0'

At present I'm having to do a find and replace - but on large files
this takes too long. Can someone recomend a good way to do this?

Thanks again,
Sab.

 

[Alan]

One way using VLOOKUP,
In some empty cells create a table like
1 2
A A+ 10
B A 9
C A- 8
and so on, then in the new column use
=VLOOKUP(K1,$A$1:$C$2,2,False) and copy down,
change the cell references to suit,
Regards,

 

[Gord Dibben]

Couple of suggestions.........

From Peo Sjoblom....
=IF(A1="","",VLOOKUP(A1,{0,"F";0.6,"D";0.7,"C";0.8,"B";0.9,"A"},2))

From Jason Morin......
=LOOKUP(A1*100,{0,60,70,80,90},{"F","D","C","B","A"})

NOTE: the use of the {}curly braces internally.

Adjust numbers and letter grades to suit.

Gord Dibben Excel MVP

 

[Ron de Bruin]

Or

http://www.cpearson.com/excel/excelF.htm#Grades