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

 

[suechetty]

Good catch! Here is a fix (the formula is still normally-entered)...

=IF(OR(LEN(G2)>1,AND(MAX(LEN(H2),LEN(I2))<=LEN(G2),CODE(G2)<=MIN(CODE(H2),CODE(I2)))),"OK","No")

And if the OP is willing to accept TRUE or FALSE answer, this (still normally-entered)...

=OR(LEN(G2)>1,AND(MAX(LEN(H2),LEN(I2))<=LEN(G2),CODE(G2)<=MIN(CODE(H2),CODE(I2))))

True/False is good too as I'm setting a number of conditions and this is just one of them. I've used Fluff's formula and it worked fine so far. As I'm working through the spreadsheet, I have another condition I would like to flag. I did not raise this earlier as it wasn't a condition I needed at the beginning but the analysis is getting bigger as I'm working through it. Here goes: I'm setting a rule to flag any scores that is ending with these scores:
34
39
49
59
69
79
with a further condition. I'll try to explain in this table below with the formulas. See Rule 1.

Overall Score	SCORE 1	SCORE 1 GRADE 1	GRADE 2	GRADE 3	Condition 1 Highest Grade	Condition 2 All 3 Grade Match	Condition 3 SCORE 1 Grade 1 & Grade 3 Match	Rule 1
62	59	C	A*	B	OK	NO	NO	IF Score1 Grade 1 is one grade below Grade 3, and Grade 1 Score is borderline (34,39,49,59,69,79), then "UPGRADE". If not "REMAIN". So this cell should state UPGRADE as it met the rule.
	CB4*1.0366-5.3034	TRIM(LOOKUP(CD4,$CV$6:$CW$13))	TRIM(AT4)	TRIM(L4)	IF(LEN(CE4)=1,IF(AND(CODE(CE4)=MIN(CODE(CE4:CG4)),SUM(--ISNUMBER(FIND("*",CF4:CG4)))=0),"OK","No"),"OK")	IF(AND(TRIM(CE4)=TRIM(CF4),TRIM(CE4)=TRIM(CG4)),"MATCH","NO")	IF(TRIM(CE4)=TRIM(CG4),"MATCH","NO")

The columns AT4 and L4 are a list of grades from other cells. All the grades are converted from scores through a lookup table.

Total Marks	Grade
0	U
35	E
40	D
50	C
60	B
70	A
80	A*
WD	WD

I hope I've provided as much information as possible here. Thank you so much in advance.

 

[Fluff]

How about

+Fluff New.xlsm
	O	P	Q	R	S	T	U	V	W
2	62	59	C	A*	B	OK	NO	NO	Upgrade
Main

Cell Formulas
Range	Formula
W2	W2	=IF(AND(CODE(Q2)=CODE(S2)+1,OR(P2={34,39,49,59,69,79})),"Upgrade","")

 

[suechetty]

How about

+Fluff New.xlsm
	O	P	Q	R	S	T	U	V	W
2	62	59	C	A*	B	OK	NO	NO	Upgrade
Main

Cell Formulas
Range	Formula
W2	W2	=IF(AND(CODE(Q2)=CODE(S2)+1,OR(P2={34,39,49,59,69,79})),"Upgrade","")

That worked but I got a problem with A* again so the +1 don't recognize A* as the higher grade. All the other letters worked.

 

[Fluff]

Ok, how about
=IF(AND(OR(CODE(Q2)=CODE(S2)+1,AND(LEN(S2)=2,Q2="A")),OR(P2={34,39,49,59,69,79})),"Upgrade","")

 

[suechetty]

Ok, how about
=IF(AND(OR(CODE(Q2)=CODE(S2)+1,AND(LEN(S2)=2,Q2="A")),OR(P2={34,39,49,59,69,79})),"Upgrade","")

perfect. I've learnt a lot on here. Thank you.

 

[Fluff]

You're welcome & thanks for the feedback.