Is there a function to give us the highest value in a list of tex

C

Calaw

Hi,
I have a list of names and I need to get the name with the highest in the
alphabetical order. I dont want to sort.
Thanks,
 
R

Ron Rosenfeld

Hi,
I have a list of names and I need to get the name with the highest in the
alphabetical order. I dont want to sort.
Thanks,
Click to expand...

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/ (Version 4.01 or later).

If by "Highest" you mean that A is higher than B and so forth, use the formula:

=VSORT(Names,,1)

If you mean Z is higher than Y and so forth, then:

=VSORT(Names)

will do.




--ron
 
D

Domenic

Here's another way...

If Z is higher than Y, try...

=INDEX(A1:A10,MATCH(LARGE(COUNTIF(A1:A10,"<"&A1:A10),1),COUNTIF(A1:A10,"<
"&A1:A10),0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

If A is higher than B, try...

=INDEX(A1:A10,MATCH(SMALL(COUNTIF(A1:A10,"<"&A1:A10),1),COUNTIF(A1:A10,"<
"&A1:A10),0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 
R

Ron Rosenfeld

Here's another way...

If Z is higher than Y, try...

=INDEX(A1:A10,MATCH(LARGE(COUNTIF(A1:A10,"<"&A1:A10),1),COUNTIF(A1:A10,"<
"&A1:A10),0))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

If A is higher than B, try...

=INDEX(A1:A10,MATCH(SMALL(COUNTIF(A1:A10,"<"&A1:A10),1),COUNTIF(A1:A10,"<
"&A1:A10),0))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
Click to expand...


Nice
--ron
 
H

Harlan Grove

Domenic wrote...
Here's another way...

If Z is higher than Y, try...

=INDEX(A1:A10,MATCH(LARGE(COUNTIF(A1:A10,"<"&A1:A10),1),
COUNTIF(A1:A10,"<>"&A1:A10),0))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

If A is higher than B, try...

=INDEX(A1:A10,MATCH(SMALL(COUNTIF(A1:A10,"<"&A1:A10),1),
COUNTIF(A1:A10,"<>"&A1:A10),0))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
Click to expand...
....

Since any instance of the 'highest' value would be as good as any
other, both formulas could be reduced to

=LOOKUP(2,1/(COUNTIF(A1:A30,">"&A1:A30)=0),A1:A30)

and

=LOOKUP(2,1/(COUNTIF(A1:A30,"<"&A1:A30)=0),A1:A30)

respectively, neither of which require array entry.
 
D

Domenic

Nice Harlan! Actually you pointed it out to me on another occasion but
I completely forgot about it. Thanks for reminding me. It's definitely
much more preferable than the one I offered.
 
R

Ron Rosenfeld

Domenic wrote...
...

Since any instance of the 'highest' value would be as good as any
other, both formulas could be reduced to

=LOOKUP(2,1/(COUNTIF(A1:A30,">"&A1:A30)=0),A1:A30)

and

=LOOKUP(2,1/(COUNTIF(A1:A30,"<"&A1:A30)=0),A1:A30)

respectively, neither of which require array entry.
Click to expand...

Nicer.
--ron
 
Top