Largest Letter

suechetty

New Member
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

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Fluff

MrExcel MVP, Moderator
Hi & welcome to MrExcel.
+Fluff New.xlsm
GHIJ
2ABCOK
3BACNo
4CBANo
5BBCOK
Master
Cell Formulas
RangeFormula
J2:J5J2=IF(AND(G2<=H2,G2<=I2),"OK","No")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G2:G5Expression=J2="OK"textNO

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

Fluff

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

suechetty

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

suechetty

New Member
=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

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

suechetty

New Member
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

MrExcel MVP
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...

-------------------

suechetty

New Member
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...

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

MrExcel MVP, Moderator
Probably be a neater way, but how about

+Fluff New.xlsm
GHIJ
2BCBOK
3CDBNo
4CCCOK
5CCCOK
6BCCOK
7BA*ANo
8CBBNo
9CCCOK
10CCBNo
11BBBOK
12A*BAOK
13AA*CNo
Master
Cell Formulas
RangeFormula
J2:J13J2=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
CellConditionCell FormatStop If True
G2:G13Expression=J2="OK"textNO

1,102,238
Messages
5,485,573
Members
407,504
Latest member
inexperiencedOne

This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...