Quantifying Abstract Academic Levels

[jjmmoo]

Hi

I'm looking for some help with a school spreadsheet for tracking pupil progress. Children are leveled in maths from 1c (very low) to 6a, (very high). 1c,1b,1a,2c,2b,2a,3c,3b,3a,4c,4b,4a,5c,5b,5a,6c,6b,6a

I would like to show their progress. So, if they have skipped from 3c to 3b a progress column could show 1. If they go from 3c to 4b the progress column would show 4.

I've attached the spreadsheet with the column I want help with highlighted in yellow

I'd really appreciate the help!

[zbor]

Why is from 3b to 3a +1 instead -1?
Also why is from 5a to 5c +2 and from 4a to 5c only +1?

I came with this but it give different solution. But please check it and give desired result based on current.

Please Login or Register  to view this content.

[Richard Buttrey]

Put the levels you show in a vertical list of 18 cells and add the two additional levels P1 & P2 that you didn't mention. Name that list of 20 cells 'level'

Then in say D4 (which I assume is a typo and should read +1 not -1) enter

Please Login or Register  to view this content.

and copy down.

[AliGW]

See the attached sheet, which uses a lookup table on the right. This table could easily be on a separate worksheet.

For everyone else's information (those who aren't au fait with National Curriculum levels), a 5c is one sub-level above a 4a: levels are numbers, sub-levels are letters, and a is higher than b, which is higher than c.

EDIT: Just noticed Richard's answer above mine, which does the same thing in a slightly different way.

[mehmetcik]

Put this formula in cell E4 and fill down

Please Login or Register  to view this content.

[shg]

Or

=(FIND(B4, "1c1b1a2c2b2a3c3b3a4c4b4a5c5b5a6c6b6a") - FIND(C4, "1c1b1a2c2b2a3c3b3a4c4b4a5c5b5a6c6b6a")) / 2

[mehmetcik]

SHG: Brillent!

[shg]

Nah, but thank you.