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.

 

[Rick Rothstein]

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.

I assume your are responding to me, correct (partial quoting would help in determining what your are responding to)? I am not following your written argument at all. Do me a favor, as I asked you for in Message #18, and show me an exact example (that is, show me values for A1 and A2) where my formula fails to return the correct value (stay within the OP's stated parameters and use letters a, b and c only).

 

[jasmith4]

A1: 6b
A2: 6d
A3: Y

A1: 6d
A2: 6b
A3: Y

Both can't be Y.

 

[Rick Rothstein]

A1: 6b
A2: 6d
A3: Y

A1: 6d
A2: 6b
A3: Y

Both can't be Y.

For some reason, you are insisting on expanding the letter range beyond what the OP said it would be (which happens to be the real system's letter limits) and, by extension, you ignored my instruction in Message #21 where I asked you to "stay within the OP's stated parameters and use letters a, b and c only". However, since you are insisting on taking the range beyond that which is required, I would point out what I said in Message #18 where I said "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." So here is the easily modified version of my formula which will work with the expanded range of letters that you are insisting upon...

=IF(SUBSTITUTE(SUBSTITUTE(LOWER(A1),"a","z"),"b","y")<=SUBSTITUTE(SUBSTITUTE(LOWER(A2),"a","z"),"b","y"),"Y","N")

If you compare this formula with my original one, you will see how easy the modification was.

 

[jasmith4]

For some reason, you are insisting on expanding the letter range beyond what the OP said it would be (which happens to be the real system's letter limits) and, by extension, you ignored my instruction in Message #21 where I asked you to "stay within the OP's stated parameters and use letters a, b and c only". However, since you are insisting on taking the range beyond that which is required, I would point out what I said in Message #18 where I said "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." So here is the easily modified version of my formula which will work with the expanded range of letters that you are insisting upon...

=IF(SUBSTITUTE(SUBSTITUTE(LOWER(A1),"a","z"),"b","y")<=SUBSTITUTE(SUBSTITUTE(LOWER(A2),"a","z"),"b","y"),"Y","N")

If you compare this formula with my original one, you will see how easy the modification was.

I don't have time to name-call, nit-pick or any of that. Besides, the OP has several working answers now. So I'll conclude by returning to your original -- and both valid and relevant -- point regarding efficiency. Although all four SUBSTITUTE functions and both LOWER functions in your formulas operate on two-character strings, I believe Scott's formula is both more readable and more efficient than yours. Let it go.

 

[XOR LX]

Also, again, with A1 containing the target and A2 the actual:

=IF(SUMPRODUCT({-1,1},MMULT({1,1},CODE(MID(A1:B1,{1;2},1))^{1;-1}))>=0,"Y","N")

Regards

 

[Rick Rothstein]

I don't have time to name-call, nit-pick or any of that. Besides, the OP has several working answers now. So I'll conclude by returning to your original -- and both valid and relevant -- point regarding efficiency. Although all four SUBSTITUTE functions and both LOWER functions in your formulas operate on two-character strings, I believe Scott's formula is both more readable and more efficient than yours. Let it go.

"Let it go"? Excuse me? In three separate messages, you went out of your way to tell me my code did not work correctly. All I have been doing is defending the fact that my code works fine as long as you apply it to the correct data (which you apparently were not doing). As for your readability claim, I said in Message #18 that I could not argue with that claim, so that point has never been at issue. As for your efficiency claim, your belief may be wrong (and for the reason I mentioned in Message #18... the added function calls between our formulas). Now, to be fair, the time difference between our formulas is probably negligible, but timing test (at least on my somewhat fast computer) would seem to indicate that when a difference occurred during testing, my SUBSTITUTE formula showed up as being slightly faster than Scott's formula. By the way, based on what the OP posted in Message #1, doesn't Scott's formula reverse the "Y", "N" outputs from what the OP asked for?

 

[Scott Huish]

Good call. Thanks!

 

[István Hirsch]

Give this formula a try (enter into A3):

=IF(7-LEFT(A1)&RIGHT(A1)>=7-LEFT(A2)&RIGHT(A2),"Y","N")