Giving Values to Letters

[CrimsonGhost]

I am doing some coursework and I wan't to be able to set up a
spreadsheet that will automatically detect if pupils are
underacheiving. I had the idea that i could have four Columns, The
pupil name,the predicted grade, and the grade they are currently
working at. and then a final fourth column with a formula to work out
underacheivement.
I Think this will work but i need to be able to assign a value to the
letters
e.g if A grade was 1 and B grade was 2 and so on, i could work out a
formula to calculate the final column

e.g. A+E/2= Grade
1+5/2=3
3=C

so if the predicted grade was A and the final Column was C then it
would be underacheivement.

Does this make any sense to anyone? Or is there any more simpe or
better way of doing this.

any help would be great! thanks

 

[BrianB]

I think a simple 2 column lookup table would do what you want.

ie. with A 1,B 2 etc. You could name the range as "Grade" and us
VLOOKUP() whenever you want the number of the grade.

eg. With your grade letter in cell A1 you would use the formula :-
=VLOOKUP(A1,Grade,2,FALSE

 

[Paul]

I am doing some coursework and I wan't to be able to set up a
spreadsheet that will automatically detect if pupils are
underacheiving. I had the idea that i could have four Columns, The
pupil name,the predicted grade, and the grade they are currently
working at. and then a final fourth column with a formula to work out
underacheivement.
I Think this will work but i need to be able to assign a value to the
letters
e.g if A grade was 1 and B grade was 2 and so on, i could work out a
formula to calculate the final column

e.g. A+E/2= Grade
1+5/2=3
3=C

so if the predicted grade was A and the final Column was C then it
would be underacheivement.

Does this make any sense to anyone? Or is there any more simpe or
better way of doing this.

any help would be great! thanks

This reference explains how to convert numbers to letters:
http://www.cpearson.com/excel/excelF.htm#Grades

You could do the reverse as well. This would enable you to convert letters
to numbers for your calculations. You would then use the above to convert
the numeric results back to letters.

 

[CrimsonGhost]

Thanks that helps quite a bit! I should be able to do it from here.
Thanks for such a speedy reply-