Largest Letter

[suechetty]

Hello,
I would like a formula to say "OK" if Grade 1 is the highest grade compared to Grade 2 and Grade 3. What formula can I use please?
Also, is there a way I can use conditional formatting to highlight Grade 1 cell if it is the highest grade?

Thank you for the help.
Example

Grade 1	Grade 2	Grade 3
A	B	B	OK
C	B	A	NO

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff New.xlsm
	G	H	I	J
1	Grade 1	Grade 2	Grade 3
2	A	B	C	OK
3	B	A	C	No
4	C	B	A	No
5	B	B	C	OK
Master

Cell Formulas
Range	Formula
J2:J5	J2	=IF(AND(G2<=H2,G2<=I2),"OK","No")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:G5	Expression	=J2="OK"	text	NO

Wasn't sure what you wanted for row 5, if it should be No then change <= to <

 

[Fluff]

If you have a lot more columns to check, you could use
=IF(G2=CHAR(MIN(CODE(G2:I2))),"OK","No")

 

[suechetty]

@Fluff Perfect! - I've tried and it worked. Thank you so much.

 

[suechetty]

=IF(G2=CHAR(MIN(CODE(G2:I2))),"OK","No")

This formula converting the letter to number might work for A* grades as the first one don't. Thank you, you are a star.

 

[Fluff]

Neither of them will work if you have grades like A*
Can you post a more realistic sample of your data.

 

[suechetty]

Neither of them will work if you have grades like A*
Can you post a more realistic sample of your data.

I've used your formula and it worked for A,B,C,D,E,U. It don't work when A* is the highest grade. I normally use tilda ~ for A~* but that don't work too.

B	C	B	OK
C	D	B	No
C	C	C	OK
C	C	C	OK
B	C	C	OK
B	A*	A	No
C	B	B	No
C	C	C	OK
C	C	B	No
B	B	B	OK
A*	A	A
A*	A	A

 

[Rick Rothstein]

Your original message said nothing about letters with asterisks. So we have the complete picture...

1) Can any other letters have an asterisk attached to them or is it only the letter A?

2) Are there any other symbols that can be attached besides an asterisk? If yes, what are they and how do they affect the grade ordering?



You might find this short advice message that I have posted in the past to be of some help with future questions...

Please Note
-------------------
For future questions you may ask, please do not simplify your question for us... doing so will get you a great answer to a question you do not actually have and which you do not actually care about AND it will almost always lead to you coming back for help when the solution we give you for the simplified question cannot be applied to your actual data and its layout. One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data).

 

[suechetty]

Your original message said nothing about letters with asterisks. So we have the complete picture...

1) Can any other letters have an asterisk attached to them or is it only the letter A?

2) Are there any other symbols that can be attached besides an asterisk? If yes, what are they and how do they affect the grade ordering?



You might find this short advice message that I have posted in the past to be of some help with future questions...

Please Note
-------------------
For future questions you may ask, please do not simplify your question for us... doing so will get you a great answer to a question you do not actually have and which you do not actually care about AND it will almost always lead to you coming back for help when the solution we give you for the simplified question cannot be applied to your actual data and its layout. One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data).

Hello,

Apologies for the incomplete picture. You are correct, I didn't mention the asterisk. A* is the only grade with special symbols. These are all the grades.
A*
A
B
C
D
E
U

Thank you so much for the help.

Suechetty

 

[Fluff]

Probably be a neater way, but how about

+Fluff New.xlsm
	G	H	I	J
1	Grade 1	Grade 2	Grade 3
2	B	C	B	OK
3	C	D	B	No
4	C	C	C	OK
5	C	C	C	OK
6	B	C	C	OK
7	B	A*	A	No
8	C	B	B	No
9	C	C	C	OK
10	C	C	B	No
11	B	B	B	OK
12	A*	B	A	OK
13	A	A*	C	No
Master

Cell Formulas
Range	Formula
J2:J13	J2	=IF(LEN(G2)=1,IF(AND(CODE(G2)=MIN(CODE(G2:I2)),SUM(--ISNUMBER(FIND("*",H2:I2)))=0),"OK","No"),"OK")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:G13	Expression	=J2="OK"	text	NO