excel function that gives the highest text entry

[curious]

is there a funtion like the max funciton but for text
instead of numbers. For example if i had a list of names
like

tom
sue
pam
zach
fred

it would return zach as it is the highest in the alphabet.

???

just curious

gordon

 

[Peo Sjoblom]

If you mean the first letter you can use

=INDEX($A$1:$A$5,MATCH(MAX(CODE(TRIM(LEFT($A$1:$A$5)))),CODE(TRIM(LEFT($A$1:
$A$5))),0))

entered with ctrl + shift & enter
where A1:A5 holds the names from you example list

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Harlan Grove]

If you mean the first letter you can use

=INDEX($A$1:$A$5,MATCH(MAX(CODE(TRIM(LEFT($A$1:$A$5)))),
CODE(TRIM(LEFT($A$1:$A$5))),0))

....

Using the first character only seems a bit restricting.

=INDEX(A1:A5,MATCH(MAX(MMULT(--(TRIM(A1:A5)>=TRANSPOSE(TRIM(A1:A5))),
ROW(A1:A5)^0)),MMULT(--(TRIM(A1:A5)>=TRANSPOSE(TRIM(A1:A5))),
ROW(A1:A5)^0),0))

And if the TRIM calls were unnecessary,

=INDEX(A1:A5,MATCH(MAX(COUNTIF(A1:A5,"<="&A1:A5)),
COUNTIF(A1:A5,"<="&A1:A5),0))

 

[Guest]

I have tested this and yep it works. Excellent work.
How did you ever figure that one out!!!

I always assumed the maxa function would do the trick
until I tried it one day and it didn't do what I
expected. So this is good to know.

Gordon