Grades to numbers

[garyrobbo]

Morning

I have a spreadsheet where I have pupil target grades and achieved grades. I would like to convert the 'letter grade' into a 'number grade' showing the difference between achievement.

For instance:

• Target grades go from A* to D
• Achieved grades go from A* to C
• If a pupil has a target of a D but achieves a grade A he will be = 3 grades aboove this target
• One more example, pupil had a target of an A, but achieved a C, so would = -2 below target

Any ideas?

Thanks

[samba_ravi]

Pls. attach a sample excel file

[Richard Buttrey]

HI,

Maybe a VLOOKUP Table with all permuations of AA, AB, AC, AD, BA, BB, BC....etc in one column and alongside the number associated with the target and achieved grades, i.e. 0, 1, 2, 3, -1,0,1 ...etc

That's the pragmatic approach.

Or you could use the CODE function. e.g.

Please Login or Register  to view this content.

where A1 is the Achieved and B1 the Target

[Pete_UK]

Assuming achieved grade is in column A, and target grade is in column B, then you can have this in C1:

=CODE(SUBSTITUTE(B1,"A*","@"))-CODE(SUBSTITUTE(A1,"A*","@"))

Hope this helps.

Pete

[garyrobbo]

Thanks that worked a treat!

[Pete_UK]

Thanks for the rep - If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

I think it would be even more robust if you did this:

=CODE(SUBSTITUTE(UPPER(B1),"A*","@"))-CODE(SUBSTITUTE(UPPER(A1),"A*","@"))

then you could use upper or lower case letters to record the grades.

Pete