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

 

[Rick Rothstein]

Probably be a neater way, but how about

IF(LEN(G2)=1,IF(AND(CODE(G2)=MIN(CODE(G2:I2)),SUM(--ISNUMBER(FIND("*",H2:I2)))=0),"OK","No"),"OK")

I think this will also work...

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

 

[suechetty]

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

Thank you, initially it didn't work for all the rows but that's because there are leading spaces before and after the grades as it's taken from different sources but I've added TRIM() in each of the G:I cells and the formula worked perfectly. Thank you once again. Saved me hours of manual check.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Dave Patton]

You can consider the following.
This was prepared on Excel 2010; consequently, they require array enter CSE.

T10_1512a.xlsm
	G	H	I	J
1	Grade 1	Grade 2	Grade 3	Grade 1 Highest
2	A	B	C	TRUE
3	B	A	C	FALSE
4	A*	B	C	TRUE
5	A	A*	C	FALSE
6	A*	A*	C	TRUE
2d

Cell Formulas
Range	Formula
J2:J4	J2	=CODE(G2)<=(MIN(CODE(H2:I2))-IFERROR(FIND("*",H2:I2),0))
J5:J6	J5	=(CODE(G5)-LEN(G5))<=(MIN(CODE(H5:I5))-IFERROR(FIND("*",H5:I5),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Rick Rothstein]

The OP wanted "OK" or "No" as the answers, but he was willing to accept TRUE and FALSE, the logical expression from my formula in Message #11 could be use...

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

Note that this is not an array formula so it can be entered normally into the cell.

 

[Fluff]

Rick, with your formula I get a false +ve, on the last row of this data.

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

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")
K2:K13	K2	=IF(OR(LEN(G2)>1,CODE(G2)<=MIN(CODE(H2),CODE(I2))),"OK","No")

 

[Dave Patton]

I thought I deleted the previous message.
I noticed a problem at the last minute.
You can consider the following. It was prepared with Excel 2010; consequently, the array formulas must be array entered.

T10_1512a.xlsm
	G	H	I	J
1	Grade 1	Grade 2	Grade 3	Grade 1 Highest
2	A	B	C	TRUE
3	B	A	C	FALSE
4	A*	B	C	TRUE
5	A	A*	C	FALSE
6	A*	A*	C	TRUE
7
2d

Cell Formulas
Range	Formula
J2:J6	J2	=(CODE(G2)-IF(LEN(G2)>1,1,0))<=MIN(CODE(H2:I2)-ISNUMBER((FIND("*",H2:I2))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

As it happens that formula will need CSE entry in all versions, because if entered normally with dynamic arrays it will spill.

 

[Rick Rothstein]

Rick, with your formula I get a false +ve, on the last row of this data.

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))))

 

[Dave Patton]

T10_1512a.xlsm
	G	H	I	J
1	Grade 1	Grade 2	Grade 3	Grade 1 Highest
2	A	B	C	TRUE
3	B	A	C	FALSE
4	A*	B	C	TRUE
5	A	A*	C	FALSE
6	A*	A*	C	TRUE
2d

Cell Formulas
Range	Formula
J2:J6	J2	=IF(SUMPRODUCT(--(LEN(G2:I2)=2)),(G2="A*"),CODE(G2)<=MIN(CODE(H2),CODE(I2)))