Is this in column or a cell?Hello,
I have a list in increasing order of value
A,B,C,...,Z,AA,AB,AC,...,AZ,BA,BB,...,BZ (so on)
I want to find the maximum in this list. max should be "BZ" and minimum should be "A" in the above list.
Any suggestions.
Thanks.
Is this in column or a cell?
Are there only 1-2 letters values?
Excel 2010 | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | A | A | AA | AB | AC | AZ | C | G | AF | |||
2 | ||||||||||||
3 | AZ | |||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C3 | {=INDEX($A$1:$J$1,1,MATCH(LARGE(IF(A1:J1<>"",IF(LEN(A1:J1)=1,CODE(MID(A1:J1,1,1)),CODE(MID(A1:J1,1,1))+CODE(MID(A1:J1,2,1)))),1),IF(A1:J1<>"",IF(LEN(A1:J1)=1,CODE(MID(A1:J1,1,1)),CODE(MID(A1:J1,1,1))+CODE(MID(A1:J1,2,1)))),0))} | |
Press CTRL+SHIFT+ENTER to enter array formulas. |
I have a list in increasing order of value
A,B,C,...,Z,AA,AB,AC,...,AZ,BA,BB,...,BZ (so on)
In that case, try ..There are only one and two letters. The list is in a row.
Excel Workbook | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
2 | A | D | E | V | AB | AC | AJ | AK | |||
3 | |||||||||||
4 | AK | ||||||||||
rarmn |
Would this work for not sorted range?
No, it would always return the right-most entry.Would this work for not sorted range?
Excel 2010
A B C D E F G H I J 1 A A AA AB AC AZ C G AF 2 3 AZ
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>Sheet1
Array Formulas Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Cell Formula C3 {=INDEX($A$1:$J$1,1,MATCH(LARGE(IF(A1:J1<>"",IF(LEN(A1:J1)=1,CODE(MID(A1:J1,1,1)),CODE(MID(A1:J1,1,1))+CODE(MID(A1:J1,2,1)))),1),IF(A1:J1<>"",IF(LEN(A1:J1)=1,CODE(MID(A1:J1,1,1)),CODE(MID(A1:J1,1,1))+CODE(MID(A1:J1,2,1)))),0))}
<thead>
</thead><tbody>
</tbody>
Note: Do not try and enter the {} manually yourself
<tbody>
</tbody>