How to calculate progress between grades like 4a and 6c

[Kingcop]

Pupils' grades are written as 2a, 3a, 3b, 4c, 5c, etc. - "a" being the higest
and "c" the lowest. I need to set up a formula to calculate the progress made
between grades assuming that one letter reperesents one point, e.g. 2c to 4a
would be 8 points, 3b to 5b would be 6 points and so on.

 

[pkaraffa]

Pupils' grades are written as 2a, 3a, 3b, 4c, 5c, etc. - "a" being the higest
and "c" the lowest. I need to set up a formula to calculate the progress made
between grades assuming that one letter reperesents one point, e.g. 2c to 4a
would be 8 points, 3b to 5b would be 6 points and so on.

If your dataset consists of a to c and you go from lets say 2 to 5,
2A
2B
2C
3A
3B
3C
4A
4B
4C
5A
5B
5C

From the scale above, I don't quite understand how 2C to 4A = 8?

Please clarify.
Thanks

 

[carlo]

Hi Kingcop

I would convert the grades into numbers with the following formula:
=(LEFT(A1,1)-1)*3+CODE(UPPER(RIGHT(A1,1)))-64
A1 being your grade.
so for 1a it would return 1, for 3c it would return 9 and so on....

after that you can use this number to calculate the points.

if you want to have it in one cell it would be like that:
=((LEFT(A1,1)-1)*3+CODE(UPPER(RIGHT(A1,1)))-64) -
((LEFT(B1,1)-1)*3+CODE(UPPER(RIGHT(B1,1)))-64)
A1 being your first grade, B1 your second

hth

Carlo

 

[carlo]

Hi Kingcop

I would convert the grades into numbers with the following formula:
=(LEFT(A1,1)-1)*3+CODE(UPPER(RIGHT(A1,1)))-64
A1 being your grade.
so for 1a it would return 1, for 3c it would return 9 and so on....

after that you can use this number to calculate the points.

if you want to have it in one cell it would be like that:
=((LEFT(A1,1)-1)*3+CODE(UPPER(RIGHT(A1,1)))-64) -
((LEFT(B1,1)-1)*3+CODE(UPPER(RIGHT(B1,1)))-64)
A1 being your first grade, B1 your second

hth

Carlo