Thread: Trouble with CODE/IF functions

Any help would be very much appreciated on this one. for a course i am doing i am required to make a spreadsheet tracking student grades, which i have completed apart from one snag.
I have used the CODE function to convert the grades to numerical values, e.g. A is 65, B is 66 etc. which has worked fine, except for the difference between F and U, which are only one grade apart yet 5 numbers apart.
This has caused problems in a function which i am using to show how many grades above or below their predicted grade a student is, as when a student is predicted a F but acheives a U they are shown to be 5 grades below their predicted grade. the formula i used to show this is =IF(CODE(F8)=CODE(E8),0,CODE(F8)-CODE(E8))
F8 being the predicted grade, E8 being actual grade.

I am trying to find a way which will show that a student is only one grade below their predicted if they are predicted a F but acheive a U. prefereably without using a LOOKUP

Thanks everyone

Unfortunately, seems to show -3 regardless of what grades are entered in either columns. trying to work out why as we speak

Examples:

       ----A---- --B--- --C-- ----------------------------D----------------------------
   1   Predicted Actual Delta                                                          
   2       A       A      0   C2 and down: =MATCH(A2, Grades, 0) - MATCH(B2, Grades, 0)
   3       A       B     -1                                                            
   4       A       C     -2                                                            
   5       A       D     -3                                                            
   6       A       U     -4                                                            
   7       A       F     -5                                                            
   8       B       A      1                                                            
   9       B       B      0                                                            
  10       B       C     -1                                                            
  11       B       D     -2                                                            
  12       B       U     -3                                                            
  13       B       F     -4                                                            
  14       C       A      2

i will try the match function again but couldn't get it to work beforehand

Got it working thanks guys for your help