Compare mixed text and number cells (Student levels)

[atmosworld]

Hello, I was wondering if any one could help me. I have been searching for hours on the web for an answer. I have a mark book with two columns, students target grade and students current grade. I work in a UK school and we level are students using 4c, 4b, 4a, 5c, 5b, 5a, 6c, 6b, 6a.etc. I am looking for a way to indicate if a student has met their target grade.
For example if a students target grade is 5c (Cell A1) and they got a 5a (Cell A2), I would like to place a 'Y' in Cell A3 as they have achieved above their target. Is there anyway of doing this. Any help would be very mush appreciated. Thank you.

 

[jasmith4]

Rick: say it isn't so! You're messing with CODEs?? Here's a direct and working way, which is also case-insensitive:

In the original post A1 is the target, and A2 is the actual, so here's what the formula should be:

=IF(LEFT(A2,1)>LEFT(A1,1),"Y",IF(LEFT(A2,1)<LEFT(A1,1),"N"< font>,IF(UPPER(RIGHT(A2,1))<=UPPER(RIGHT(A1,1)),"Y","N")))

Green: if the first character (the digit) of the actual is higher than that of the target, student passes.

Red: if the first character (the digit) of the actual is lower than that of the target, student fails.

Purple: if the first characters are equal, it depends on whether the last character of the actual is not higher than that of the target, regardless of case.

Note that if A1=A2 the answer is Y, because the student met the target. If this should not happen, change <= to just <.

 

[Scott Huish]

I got a #VALUE! error with that formula

 

[Scott Huish]

This seems to work:

=IF(LEFT(A2)>LEFT(A1),"Y",IF(AND(LEFT(A2)=LEFT(A1),UPPER(RIGHT(A2))>=UPPER(RIGHT(A1))),"Y","N"))

 

[jasmith4]

You have to populate A1 and A2 with valid British grades.

 

[Rick Rothstein]

This seems to work:

=IF(LEFT(A2)>LEFT(A1),"Y",IF(AND(LEFT(A2)=LEFT(A1),UPPER(RIGHT(A2))>=UPPER(RIGHT(A1))),"Y","N"))

If, as jasmith4 suggests, that using CODE is a bad idea (personally, I do not see why), then here is another formula which does not use that function and requires 4 less functions than the modified jasmith4 formula you posted...

=IF(SUBSTITUTE(SUBSTITUTE(LOWER(A1),"a","e"),"b","d")<=SUBSTITUTE(SUBSTITUTE(LOWER(A2),"a","e"),"b","d"),"Y","N")

although I do not know how the SUBSTITUTE functions compare efficiently-wise to the IF, AND, LEFT and RIGHT functions used by jasmith4.

 

[jasmith4]

Scott: I think you have to specify LEFT(A1,1) and RIGHT(A2,1). Other than that your formula seems to reflect fewer logical steps.

Rich: First, CODE makes your formula case-sensitive, which might be an issue. Second, CODE renders your formula "programmerese" in that you happen to know that a lowercase E is character #101 in ASCII (I'd rather see CODE("e") than 101 for readability); plus you're assuming "e" is the highest letter, a limitation not present in Scott's and my formulas.

In any case, I see what you're trying to do, but it doesn't work! Let's say, you're comparing 6b to 6d or vice versa: either "6b" will change to "6d" OR vice-versa, making the strings equal when the "<=" operation is performed!

 

[Scott Huish]

You don't have to have the ,1 for LEFT and RIGHT. If it is not specified, it is 1 by default.

 

[Rick Rothstein]

Rick: First, CODE makes your formula case-sensitive, which might be an issue.

Easily overcome, if necessary, using an UPPER (or possibly a LOWER) function call in the same way that you did for your formula.

Second, CODE renders your formula "programmerese" in that you happen to know that a lowercase E is character #101 in ASCII (I'd rather see CODE("e") than 101 for readability)

While I cannot argue with that, I would point out that introducing extra function calls makes Excel work harder which, if enough cells have the formula, would mean efficiency of the worksheet could suffer.

...plus you're assuming "e" is the highest letter, a limitation not present in Scott's and my formulas.

That is the model that was presented to us; however, if the OP "lied", my formula can be easily modified to handle a larger or full alphabet.

In any case, I see what you're trying to do, but it doesn't work! Let's say, you're comparing 6b to 6d or vice versa: either "6b" will change to "6d" OR vice-versa, making the strings equal when the "<=" operation is performed!

Can you show me an exact example of where my formula fails (not sure which of my two formulas you are talking about, by the way)?

 

[jasmith4]

Compare nA to nE or nB to nD, where n is any digit, and you keep them equal. No matter which goes in A1, and which goes in A2, you'll get a Y. Step through Formula / Evaluate Formula, and after 8 steps you'll see it compare equal strings. This is because one, but not both, SUBSTITUTE functions changes the string.

 

[jasmith4]

Re CODE(101): Efficiency vs readability is an age-old argument, so all I'll say in general is that I think I lean toward readability a little more than you. In the context of sorting, searching, or graph or numerical algorithms, easy/readable/sensible ways are usually much more inefficient. But in this context I think the difference in efficiency is so small, and the readability difference so great, that my favorite formula of all those posted here is Scott's: fewer steps than mine, clear and logical steps, case-insensitive, no programmerese, and able to handle the whole alphabet, even if only 20% is necessary. If I wrote a formula like yours, I know I'd come back to it in a month and say "What was I THINKING?".