Trouble with CODE/IF functions

**Darknessfalls** · 04-26-2008, 12:34 PM

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

**mrice** · 04-26-2008, 12:39 PM

Try nested IF functions

=IF(CODE(F8)=CODE(E8),0,IF(CODE(E8)<>CODE("U"),CODE(F8)-CODE(E8),CODE(F8)-CODE("F")))

**shg** · 04-26-2008, 12:49 PM

Perhaps =MATCH(A1, {"A","B","C","D","U","F"}, 0) - MATCH(B1, {"A","B","C","D","U","F"}, 0)

If you define (Insert > Name > Define) the array constant Grades Refers to: = {"A","B","C","D","U","F"}, then you can use

=MATCH(A1, Grades, 0) - MATCH(B1, Grades, 0)

**Darknessfalls** · 04-26-2008, 12:54 PM

No luck, with nested if, when tried if a student was predicted a B but got a U it shows as being 3 grades below the predicted rather than 5
Also no luck with the MATCH function. shows grades with E as predicted and U as actual as being -1 rather than -2, and using F as predicted and U as actual it shows +1 i.e they were one above their predicted
thanks anyway

**mrice** · 04-26-2008, 01:00 PM

Any better?

=IF(CODE(F8)=CODE(E8),0,IF(CODE(E8)<>CODE("U"),CODE(F8)-CODE(E8),CODE(F8)-CODE("G")))

**Darknessfalls** · 04-26-2008, 01:06 PM

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

**shg** · 04-26-2008, 01:09 PM

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