Count difference between two values

[jwillis07]

Hi all,

Need some help pinpointing a formula or work around that would be able to count the difference between two non-standard values.

For background info... I work in a school as Data Manager and I'm trying to work out how many sub-levels of progress Year 7 students have made between their Key Stage 2 (Year 6) results and their end of Year 7 results.

For instance, the results possible for Key Stage 2 would be in the format of a number, then letter. i.e... (3c - the numbers go from 1 to 5, and the letters a to c so there's 15 different possibilities. (1c, 1b, 1a, 2c, 2b, 2a etc). If a student gained a 3c in Year 6 they would be expected to gain a 4c in Year 7 which is 3 sub-levels of progress.

So I have two columns, A is Year 6 and B is Year 7.

---A---B---
1 2c 2b
2 1b 2c
3 1a 2b
4 3c 2a

What I need is column C to show how many sub-levels of progress have been made or lost.

---A---B---C---
1 2c 3c + 3
2 2a 1c - 1
3 1a 2b + 4
4 3c 4b + 2


For reference, sub-levels in rank order low-high...

1a, 1b, 1c, 2a, 2b, 2c, 3a, 3b, 3c, 4a, 4b, 4c, 5a, 5b, 5c




I understand what I'm asking may be difficult so I'm not asking for a final solution from you guys (if you can, great!) but even if you could point me in the right direction of how I can do this that would be most helpful and greatly appreciated!

Many thanks in advance,

Jason

[TPDave]

File attached that should do it. Obviously you can move the list of sub levels elsewhere and change the reference in the formulas.

Thanks

[jwillis07]

Wow, I'm impressed! Thank you very much Dave.

[ICTjunkie]

This is great but I need to do this across 4 Levels End of year, Autumn, Spring, Summer but the calculation needs to happen each termly input. So I have the end of year and now the Autumn, how much sub level process have they made same as above levels 1C, 1B, 1A etc. to 5A 1 sub level point each. Thanks in advance.