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.

 

[Momentman]

Not familiar with Uk grading system but how does one know for example that 5A is higher than a 5C and so on, to make it easy to put in A Y or an N?

 

[jasmith4]

It's a simple lexicographic comparison. A3's formula should just be =IF(A2<=A1,"Y", "N")

 

[atmosworld]

The uk grading system goes up up like this, 2c > 2b > 2a > 3c >3b >3a all the way up to 7c > 7b >7a this is used for out 11 - 14 year olds (Middle school?). It is very confusing to understand but a student will enter school, for example with a 4c they then move up to a 4b and more up to a 4a and keep on making progress as they move up through the school. Each student is predicted a grade at the end of each year, for for example Matt might be predicted to get a 6c, however he only gets a 5a (which is lower then a 6c), I want to know is there a formula that will automatically put a Y or N next to his name if he did/did not met his target?.....I really hope that makes sense (please blame the UK government if not!!!).

 

[atmosworld]

"It's a simple lexicographic comparison. A3's formula should just be =IF(A2<=A1,"Y", "N")I tried this before and it only seems to work on some values, for example comparing 6c to 5a it worked (giving me a Y because 6c is larger than 5a) but comparing 5b to 5c it did not work (gave me a N, but should have given me a Y because 5b is larger then 5c).

 

[Scott Huish]

Make a table like I did in J1:J9 of your available grades. Then in A3:
=IF(MATCH(A2,$J$1:$J$9,0)>=MATCH(A1,$J$1:J9,0),"Y","")

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	5c									4c
2	5a									4b
3	Y									4a
4										5c
5										5b
6										5a
7										6c
8										6b
9										6a
Sheet1

 

[circledchicken]

Hi,

Perhaps try something like this:

Excel 2013
	A	B	C	D	E	F
1	Student	Target grade	Current grade	Achieved target		Grade (sorted worst to best)
2	A	5c	5a	Y		2c
3	B	6c	4b	N		2b
4	C	5b	6b	Y		2a
5						3c
6						3b
7						3a
8						4c
9						4b
10						4a
11						5c
12						5b
13						5a
14						6c
15						6b
16						6a
17						7c
18						7b
19						7a
Sheet1

Cell Formulas
Range	Formula
D2	=IF(MATCH(C2, F$2:F$16, 0) >= MATCH(B2, F$2:F$16, 0), "Y", "N")

 

[atmosworld]

THANK YOU so much, it worked perfect. I spent hours looking on the web, 10 min on this site and I get the answer. You guys are amazing! Thank you

 

[circledchicken]

Thanks for the feedback!

 

[Rick Rothstein]

THANK YOU so much, it worked perfect. I spent hours looking on the web, 10 min on this site and I get the answer. You guys are amazing! Thank you

Assuming the target value is in cell B2 and the current grade is in C2 (as circledchicken showed in Message #7), and that the letters are always lower case, then you can put this formula in D2 and copy it down without the need for the sorted Grade table that circledchicken showed)...

=IF(LEFT(C2)&(101-CODE(RIGHT(C2)))>=LEFT(B2)&(101-CODE(RIGHT(B2))),"Y","N")