Subracting numbers with a letter combination

[Poppyhead66]

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

[etaf]

why is 5b - 4c = -4 ?
as i dont know your grading system

[mehmetcik]

You haven't provided enough information.

this formula will give you the point difference of the numeric grade

Please Login or Register  to view this content.

this will give you the point difference of the letter grade

Please Login or Register  to view this content.

Play around with them to meet your requirement

I will create a formula that assumes 1A is your highest and 8c is your lowest.

if you enter 1a in A1 and *c in B1

This formula in C1

Please Login or Register  to view this content.

will return 23

[Poppyhead66]

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.

[Tony Valko]

I think I would come up with an easier grading method.

[mehmetcik]

If your grades are in A1 and B1 put this in C1

Please Login or Register  to view this content.

[FDibbins]

lol @ Tony

I did this with a small table...

	A	B	C
1	1c	1	4c
2	1b	2	5b
3	1a	3	-4
4	2c	4
5	2b	5
6	2a	6
7	3c	7
8	3b	8
9	3a	9
10	4c	10
11	4b	11
12	4a	12
13	5c	13
14	5b	14
15	5a	15
16	6c	16
17	6b	17
18	6a	18
19	7c	19
20	7b	20
21	7a	21
22	8c	22
23	8b	23
24	8a	24

C1 and C2 are your grades
C3=VLOOKUP(C1,$A$1:$B$24,2,0)-VLOOKUP(C2,$A$1:$B$24,2,0)

[Poppyhead66]

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

[FDibbins]

@ Poppyhead, what that is doing, is essentially the same as I did with my table, but is a much better - single - formula.

It is breaking out the numeric, then *8. Then getting the code of the letters (which is a number) and subtracting them - then adding those 2 values together.

You can play with this by breaking down the formula into its component parts

and by the way, because you doing math on 2 text values (produced from the LEFT() function), you can do away with the VALUE() functions, so that it becomes...
=(LEFT(B1)-LEFT(A1))*3+CODE(RIGHT(A1))-CODE(RIGHT(B1))

[mehmetcik]

The code returns the ASCII code for the letter

A = 64
B = 65
C = 66

a = 97
b = 98
c = 99

Then you can subtract one from the other.

Like Ford said, I am multiplying the differrence of the numeric element by 3, because there are three steps between each numerical step.

you could make the formula more robust by using the uppercase command:-

=(LEFT(B1)-LEFT(A1))*3+CODE(upper(RIGHT(A1))-CODE(upper(RIGHT(B1)))

so you can type a or A, b or B, c or C without a problem

[Poppyhead66]

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

[Poppyhead66]

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

[mehmetcik]

Hi

A computer does not understand characters or symbols.

So every character or symbol is assigned a number an ASCII number.

ASCII = American Standard Code for Information Interchange,

[Poppyhead66]

Hi ,

Thankyou for the explanation and for the attachment. Much appreciated