Hi, I am a school teacher and when we level pupils they receive a level and
then a sub level, which is unfortunately a letter, eg: 6C (being the lowest
of three sublevels in the 6s up to 6A being the highest, then onto 7C etc).

I have a level from last year, and want to compare that to their current end
of year level. I have beein saying that 6C is 62 and 6B is 65 and 6A is 68
but I find this confusing. I'd like to be able to say using some formula or
something that if they were 6C at the start of the year and are now a 6A,
then they have moved up 2 sublevels (+2 or something). Is there any way of
doing this without using my 62, 65, 68 solution????

Thanks.

Those levels are all valid Hex numbers, so you can perform arithmetic on
them.
If you have the Analysis Toolpack installed, you can use the DEC2HEX and
HEX2DEC function.
Or a UDF could use Hex (CLng("&H" & argRange.Value) + 2)

However, the numeric order will be the other way, as 6A<6B<6C.

NickHK

"PG Oriel" wrote in message
...
Hi, I am a school teacher and when we level pupils they receive a level
and
then a sub level, which is unfortunately a letter, eg: 6C (being the
lowest
of three sublevels in the 6s up to 6A being the highest, then onto 7C
etc).

I have a level from last year, and want to compare that to their current
end
of year level. I have beein saying that 6C is 62 and 6B is 65 and 6A is 68
but I find this confusing. I'd like to be able to say using some formula
or
something that if they were 6C at the start of the year and are now a 6A,
then they have moved up 2 sublevels (+2 or something). Is there any way of
doing this without using my 62, 65, 68 solution????

Thanks.

Another solution:

Create a second sheet (Sheet2) containing all sub levels from 1A to 7C in
column A in order:
1A
1B
1C
2A
2B
2C
3A
.... etc.
Say Start level is in A2, present level in B2, then this formula in C2 gives
you the pupils move:

=MATCH(A2,Sheet2!A:A,0)-MATCH(B2,Sheet2!A:A,0)

Regards,
Stefi

€žNickHK€� ezt Ã*rta:

Those levels are all valid Hex numbers, so you can perform arithmetic on
them.
If you have the Analysis Toolpack installed, you can use the DEC2HEX and
HEX2DEC function.
Or a UDF could use Hex (CLng("&H" & argRange.Value) + 2)

However, the numeric order will be the other way, as 6A<6B<6C.

NickHK

"PG Oriel" wrote in message
...
Hi, I am a school teacher and when we level pupils they receive a level
and
then a sub level, which is unfortunately a letter, eg: 6C (being the
lowest
of three sublevels in the 6s up to 6A being the highest, then onto 7C
etc).

I have a level from last year, and want to compare that to their current
end
of year level. I have beein saying that 6C is 62 and 6B is 65 and 6A is 68
but I find this confusing. I'd like to be able to say using some formula
or
something that if they were 6C at the start of the year and are now a 6A,
then they have moved up 2 sublevels (+2 or something). Is there any way of
doing this without using my 62, 65, 68 solution????

Thanks.