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

G

Guest

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.
 
P

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
 
C

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
 
C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top