Help on is it poss to pick the (>) letter from a row ???

[rmtaylor]

Help
I am attempting without success to set up a sheet that will pick out the highest letter from a row or array similar to the example below

A B C D E Answer = E
A B D Answer = D
A B D F G H Answer = H
I would appreciate any help on this problem
Robert
Scotland

[Aladin Akyurek]

On 2002-04-11 22:58, rmtaylor wrote:
Help
I am attempting without success to set up a sheet that will pick out the highest letter from a row or array similar to the example below

A B C D E Answer = E
A B D Answer = D
A B D F G H Answer = H
I would appreciate any help on this problem
Robert
Scotland

Array-enter:

=CHAR(MAX(CODE(A2:E2)))

where each cell in A2:E2 houses a single letter.

In order to array-enter a formula, hit control+shift+enter at the same time, not just enter.

[rmtaylor]

I attempted this by adding the formula to a sheet as a test as read no sucess???

[rmtaylor]

On 2002-04-11 22:58, rmtaylor wrote:
Help
I am attempting without success to set up a sheet that will pick out the highest letter from a row similar to the example below

A B C D E Answer = E
A B D Answer = D
A B D F G H Answer = H
I would appreciate any help on this problem
Robert
Scotland

I attempted this by adding the formula to a sheet as a test as read no sucess???



[ This Message was edited by: rmtaylor on 2002-04-12 00:12 ]

[Aladin Akyurek]

On 2002-04-12 00:11, rmtaylor wrote:

I attempted this by adding the formula to a sheet as a test as read no sucess???
[ This Message was edited by: rmtaylor on 2002-04-12 00:12 ]

What did you get? An error, #VALUE! maybe?

Aladin

[jimboy]

OK, this is a long way around it...

If your text is in column 'A' and has 5 letters then in columns B,C,D,E & F enter;
=MID($B10,1,1)
=MID($B10,2,1)
=MID($B10,3,1)
=MID($B10,4,1)
=MID($B10,5,1)

Then in column G enter;
=IF(B1<>"",CODE(B1),1)
and fill right to column 'K'

then in column (L)enter;
=MAX(G1:K1)

Finally in column 'M' enter;
=CHAR(L1)
this will give you the highest letter...

[Ian Mac]

On 2002-04-12 02:22, jimboy wrote:
OK, this is a long way around it...

If your text is in column 'A' and has 5 letters then in columns B,C,D,E & F enter;
=MID($B10,1,1)
=MID($B10,2,1)
=MID($B10,3,1)
=MID($B10,4,1)
=MID($B10,5,1)

Then in column G enter;
=IF(B1<>"",CODE(B1),1)
and fill right to column 'K'

then in column (L)enter;
=MAX(G1:K1)

Finally in column 'M' enter;
=CHAR(L1)
this will give you the highest letter...

=CHAR(MAX(CODE(MID(A2,{1,2,3,4,5},1))))
will do the same job,
BUT I think the problem lies in the fact that if there is only 4 characters in the cell (or over 4 columns, as in Aladins answer), it will return #VALUE!.

I'm trying to figure a way round this, hang fire.

[Aladin Akyurek]

On 2002-04-12 02:54, Ian Mac wrote:

=CHAR(MAX(CODE(MID(A2,{1,2,3,4,5},1))))
will do the same job,
BUT I think the problem lies in the fact that if there is only 4 characters in the cell (or over 4 columns, as in Aladins answer), it will return #VALUE!.

I'm trying to figure a way round this, hang fire.

Ian

I assumed a single letter per cell in my original reply and I said so. If it is just a string in a single cell and you want the "letter" with highest ASCII value, the following array-formula will do:

=CHAR(MAX(CODE(MID(A6,ROW(INDIRECT("1:"&LEN(A6))),1))))

Aladin

[Ian Mac]

On 2002-04-12 03:06, Aladin Akyurek wrote:

Ian

I assumed a single letter per cell in my original reply and I said so. If it is just a string in a single cell and you want the "letter" with highest ASCII value, the following array-formula will do:

=CHAR(MAX(CODE(MID(A6,ROW(INDIRECT("1:"&LEN(A6))),1))))

Aladin

I also was considering them in single cell, I must admit I was playing with both idea and was getting close, alas! you've done it again.
What about the other 1 though (single cells!)

I've been trying to use INDIRECT() in the CODE() part:

=CHAR(MAX(CODE(INDIRECT(ADDRESS(ROW(A1),COLUMN(A1))&":"&ADDRESS(ROW(A1),COLUMN()-1)))))

is where I'm at. unfortunatly it doesn't work and I can't figure out why because if I use

=CHAR(MAX(CODE(INDIRECT(G2&":"&G3))))

where G2 houses

=ADDRESS(ROW(A1),COLUMN(A1))

and G3

=ADDRESS(ROW(A1),COLUMN()-1)

which I thought would be the same thing.

Any Ideas, is the INDIRECT having problems with the Array Formula entry?