excel vb help

[Dr Delphi]

I'm trying to create a VB function which compares two different KS3 scores and returns a residuals between the two, im at a bit of a loss as the residual needs to be in a form of base three for the decimal but whole numbers for the rest, i.e.:

3c
3b
3a
4c
4b
4a
etc. is the scoring, and the residuals look like:

6c -> 7c = -1.0
5a -> 3b = 2.1

Would anyone be able to help?

 

[Dr Delphi]

I'd already come up with some of those ideas, ive spent quite a few hours trying to work out the intricacies as sometimes it doesn't work because the decimal is in base 3 but not the whole number it causes problems.

The way I've had it explained by the data person in school is that the following is part of the lookup table, which then they use the residual as a whole number of sublevels (each whole level is divided into a,b,c so it goes 4a, 5c, 5b, 5a, 6c).

2c 13
2b 15
2a 17
3c 19
3b 21
3a 23
4c 25
4b 27
4a 29
5c 31
5b 33
5a 35
6c 37
6b 39
6a 41
7c 43
7b 45
7a 47

Tgt CWL Residual
5c 6b 31 39 8 4
4a 3b 29 21 -8 -4

This is all well and good when its a few to be calculated but as I am in charge of all the most able kids at school I need to be able to go through all 1200 students quickly, so I am trying to make a new function called Resid(A1, A2) where A1 is the first level (target) and A2 the second CWL level, the difference between the two would be 4 (using 5c and 6b) which needs to be reinterpreted as 1.1 whole levels.

I don't know if that helps with my query at all?

 

[Dr Delphi]

Thanks andshrew and dazzerd, the only time ive found so far that it doesn't return the value I expect is when the cwl is lower than the target, for example:
4a 5c gives 0.8 which should be 0.1 as its only one sub level increase, this is where my rusty logic from phd days started to fail me as I could never make it work for all the possible cases!