Assign Numbers to Letters to get Max Letter

[kisshenry]

Say you have a column A through F. With A being min, B greater than A, C greater than B, etc. So, then F would be the maximum. Now, what I want to do is return F (the maximum) in another cell. I know with numbers you just use =max(num1,num2…) but how do you do it with letters? I want to avoid making several tables and doing a vlookup kinda thing. I really want to do all of this in a single cell. Help anyone?

<o> </o>
<table class="MsoNormalTable" style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 13.35pt;"> <td style="border: 1pt solid black; padding: 0in 5.4pt; width: 100.05pt; height: 13.35pt;" valign="top" width="167"> A[FONT=&quot]<o></o>[/FONT]
</td> <td style="border-style: solid solid solid none; border-color: black black black -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0in 5.4pt; width: 100.05pt; height: 13.35pt;" valign="top" width="167"> [FONT=&quot]<o> </o>[/FONT]
</td> </tr> <tr style="height: 13.35pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color black black; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 100.05pt; height: 13.35pt;" valign="top" width="167"> B[FONT=&quot]<o></o>[/FONT]
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 100.05pt; height: 13.35pt;" valign="top" width="167"> [FONT=&quot]<o> </o>[/FONT]
</td> </tr> <tr style="height: 13.35pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color black black; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 100.05pt; height: 13.35pt;" valign="top" width="167"> C[FONT=&quot]<o></o>[/FONT]
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 100.05pt; height: 13.35pt;" valign="top" width="167"> [FONT=&quot]<o> </o>[/FONT]
</td> </tr> <tr style="height: 13.35pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color black black; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 100.05pt; height: 13.35pt;" valign="top" width="167"> D[FONT=&quot]<o></o>[/FONT]
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 100.05pt; height: 13.35pt;" valign="top" width="167"> [FONT=&quot]<o> </o>[/FONT]
</td> </tr> <tr style="height: 13.35pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color black black; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 100.05pt; height: 13.35pt;" valign="top" width="167"> E[FONT=&quot]<o></o>[/FONT]
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 100.05pt; height: 13.35pt;" valign="top" width="167"> [FONT=&quot]<o> </o>[/FONT]
</td> </tr> <tr style="height: 13.35pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color black black; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 100.05pt; height: 13.35pt;" valign="top" width="167"> F[FONT=&quot]<o></o>[/FONT]
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 100.05pt; height: 13.35pt;" valign="top" width="167"> [FONT=&quot]<o> </o>[/FONT]
</td> </tr> <tr style="height: 13.35pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color black black; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 100.05pt; height: 13.35pt;" valign="top" width="167"> F[FONT=&quot]<o></o>[/FONT]
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 100.05pt; height: 13.35pt;" valign="top" width="167"> <-- Cell returns Max letter.[FONT=&quot]<o></o>[/FONT]
</td> </tr> </tbody></table>

 

[barry houdini]

Hello kisshenry, welcome to MrExcel

You could use this formula to get the MAX letter

=CHAR(MAX(IF(A1:A10<>"",CODE(A1:A10))))

This is an array formula that needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar

 

[mooseman]

That is great... I didn't know about the CTRL+SHIFT+ENTER for array formula.

I was trying to get it to work and found this if the letters are lower and upper case

=CHAR(MAX(IF(A1:A10<>"",CODE(UPPER(A1:A10)))))

Although this returns the letter as a capital no matter what.

 

[kisshenry]

Thank sooooo much! This is EXACTLY what I was looking for. I'd asked several friends and other professionals and kept getting solutions that required making tables, nested if/then statements etc. This is very simple and to the point. You made my day!!!

 

[West Man]

Another way that I obtained from this site is:
=LOOKUP(2,1/(COUNTIF(A1:A7,">"&A1:A7&"~")=0),A1:A7)