Dear Ron,
This looks really promising, but I haven't installed the new functions yet.
Nevertheless, I will gladly look into that and let you know more.

Thanks a lot.

"Ron Rosenfeld" wrote:

On Sat, 29 Mar 2008 08:05:00 -0700, FiluDlidu
wrote:

I know SMALL and LARGE don't work on text. Are there alternate functions
that could sort text and find the x-th smaller/larger "value" among the cells
of the range?

I don't know of any.

I think you would have to write a UDF, that would return a sorted array of the
values, and then use the INDEX function to select the ranking you want.

You could use Longre's free morefunc.xll add-in

http://xcell05.free.fr/morefunc/english/

and use the UNIQUEVALUES function. That can return an array up to 65,536 cells
sorted in either ascending or descending order. You could then use the INDEX
function to find the x-th largest or smallest item.

For example:

=INDEX(UNIQUEVALUES(rng,1),1)

would return the "smallest" text value in rng.

"smallest" would be the topmost entry in a case-sensitive ascending sort.


--ron