counting grades

  • Thread starter Thread starter Dingbat
  • Start date Start date
D

Dingbat

Hello
In a worksheet, I have rows of numerous students and a column with their
achieved grade and a column with their target grade per subject.
So each subject has 2 columns and there are around 14 subjects.
I would like to add a 3rd column for each subject with a formula that would
calculate the difference.
But the grades are A, B, C etc to F and not numbers.
Can anyone help me with this please.

ie
Name ART TARGET ART DIFF BUS STU TARGET BU ST DIFF
JO A B -1 B
A +1

DIFF being the column with the formula

Ta
D
 
Try this...

A1 = A
B1 = B

This formula is case sensitive!

=CODE(B1)-CODE(A1)

Returns 1

=CODE(A1)-CODE(B1)

Returns -1

A has a lower code value than B. B has a lower code value than C. C has a
lower code value than D. etc.

Upper case and lower case letters have different code values. "A" does not
equal "a".
 
I would set up a small table with the Grades and the values they represent.
A 1
B 2
C 3
D 4
F 5

Then use this formula:

=VLOOKUP(B2,$A$21:$B$25,2,FALSE)-VLOOKUP(C2,$A$21:$B$25,2,FALSE)

Assuming B2 is the Target grade
C2 is the Actual Grade
A21:B25 is the table holding the grades and their assigned values

You should get a result similar to this:
Name Art Diff Target Diff
Bill A B -1
Fred B A 1
Susan A C -2

Hope that helps.

Frank
 
Thank you for this.
Would work great but there are lots of A* so it doesn't work for those.
D
 
Thank you very much.
This worked well. is there something I can add in front so that, if there is
no grade, it prevents #N/A from appearing?

Also I've started to have the weirdest thing happen. You know when you go
into a cell with a formula, it then colours up the cells being formulated,
and you can either Esc or change something and Enter to apply? Done this for
years but for some reason when I press Enter instead of the result
appearing, I'm getting the actual formula in the cell. as though it is
ignoring the =.
Never seen this before and very irritating because the formula it correct
but can't do anything. If I undo it'll work but then I can't change the
formula

I'm using Excel 07
Thanks for all advice
D
 
Yes, it'll work.

A1 = A*
B1 = D

=CODE(A1)-CODE(B1)

Returns -3

=CODE(B1)-CODE(A1)

Returns 3

As written, the CODE function will only look at the very first character in
the cell. So, the * in A1 is ignored.
 
Back
Top