Teaching Sub-Levels and Excel Nightmare

[PG Oriel]

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.

 

[NickHK]

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

 

[Stefi]

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: