Largest Letter

suechetty

New Member
Joined
Jul 4, 2020
Messages
9
Office Version
365
Platform
Windows
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 1Grade 2Grade 3
ABBOK
CBANO
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
42,590
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about
+Fluff New.xlsm
GHIJ
1Grade 1Grade 2Grade 3
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
Joined
Jun 12, 2014
Messages
42,590
Office Version
365
Platform
Windows
If you have a lot more columns to check, you could use
=IF(G2=CHAR(MIN(CODE(G2:I2))),"OK","No")
 

suechetty

New Member
Joined
Jul 4, 2020
Messages
9
Office Version
365
Platform
Windows
=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
Joined
Jun 12, 2014
Messages
42,590
Office Version
365
Platform
Windows
Neither of them will work if you have grades like A*
Can you post a more realistic sample of your data.
 

suechetty

New Member
Joined
Jul 4, 2020
Messages
9
Office Version
365
Platform
Windows
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 CBOK
C DBNo
C C COK
C C COK
BC COK
B A*ANo
C B BNo
C C COK
C C BNo
B BBOK
A*AA
A*AA
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,032
Office Version
2010
Platform
Windows
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

New Member
Joined
Jul 4, 2020
Messages
9
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,590
Office Version
365
Platform
Windows
Probably be a neater way, but how about

+Fluff New.xlsm
GHIJ
1Grade 1Grade 2Grade 3
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
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top