Teaching Sub-Levels and Excel Nightmare

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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:
 
Back
Top