excel function that gives the highest text entry

  • Thread starter Thread starter curious
  • Start date Start date
C

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
 
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
 
Peo Sjoblom said:
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))
Click to expand...
....

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))
 
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
 
Back
Top