Subracting numbers with a letter combination

Dear All

i am new to this forum and am relatively new to working with spreadsheets. Please could someone advise me if there is a formula or a function i can use to subract numbers which are listed as fine grades using a combination of numbers and grades .

For example if I wanted to subtract 4c from 5b then I would be left with difference of minus 4 fine grades .

The numbers I need to use will range from 1 - 8 and the fine grading system is c-a with c being the lowest grade and -a the highest.

I am familiar with both IMSUB and LOOKUPS and am wondering if i will manually have to work out the difference between the scores and then input or if it will be necessary to create a Lookup page which will incorporate a variety of subtraction models or is there a formula which I can apply.

Unfortunately , I don't know all of the techinical names for the various functions and am hoping that some one who is more knowledgeable in this field can provide me with more time effective option .

Many thanks in anticipation of a successful response

hello and thank you for a quick response.

you are correct i need to explain myself further

Our grading system is allocated in levels with a fine grade .

Each number 1-8 is associated with a letter a-b

Numerically level 1 is the lowest and 8 the highest . Within each level the letter categorises how much into the level a student is at so for example
if a child has achieved a level 4c then he / she is only just beginning to hit that level . however if a child achieves a level 4a then he or she is close to a level 5.

What i would like to do is compare the difference between a forecast level and an achieved level,
so for example if a child has a forecast grade of a 4b but he / she has achieved a level 2b then he / she has 6 fine levels of underachievement ( taking into account 2a,3c,3b,3a,4c ) and would be seen as -6 below the target grade .

I would like a formula or the method to create a formula which would count up the levels of difference between the forecast and the attained which will work across numbers

eg Forecast 6b attained 5c difference -4
Forecast 4a attained 4a difference 0
Forecast 5c attained 5b difference +1

i apologise for being longwinded, I need to save some time instead of counting up each figure and then inputting the difference.

Many thanks again for your time and suggestions.

Hello Mehmet,

When i read your post yesterday , I must confess I didn't understand the formula you wrote and didn't open your spreadsheet. I have since opened your spreadsheet and it is exactly what I needed. thanyou so much, it does exactly as I had hoped. i am very grateful to you, having just had a "play" with it.

If you could spare me the time I would be very grateful if you could explain how the formula you wrote works, unfortunately / or fortunately , i am one of those people who likes to be able to understand so that i can apply things learnt .

Many thanks once again, your help has saved me alot of frustration

Hello there FDibbens,

Thankyou for your time and explanation, I sort of understand what you have wrote , but will need to re-read and "play" with your formula in order to be able to understand it fully. I wish i knew alot more about spreadsheets but in time and practice I will improve.
I am very grateful to you for your help

Could you please clarify what ASC11 code is please ? and are the numbers you have written against the letters used as examples ?
thanks

Hi ,

Thankyou for the explanation and for the attachment. Much appreciated