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?

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

Let's say A1 thru A10 contains:

qwerty
double
nhsf
qaz
awqr
bgty
kiop
lkmn
bx
edc

If we assume that "SMALL()" follows the normal sort order then awqr would be
the "smallest"

In another cell, enter the array formula:

=INDEX($A$1:$A$10,MATCH(SMALL(COUNTIF($A$1:$A$10," <"&$A$1:$A$10),3),COUNTIF($A$1:$A$10,"<"&$A$1:$A$1 0),0))

this returns bx which is the third smallest. An array formula is entered
with CNTRL-SHFT-ENTER rather than just ENTER.

to get the seventh smallest, just substitute a 7 for the 3.
--
Gary''s Student - gsnu200776 gsnu2007xx


"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?

Perhaps something like this:

Using this TEXT ONLY list
A1: one
A2: two
A3: three
A4: four
A5: five
A6: six
A7: seven
A8: eight
A9: nine
A10: ten

B1: (the rank to find...eg 5 means the 5th smallest

So.if B1: 5, then the 5th smallest sample list item is: "one"
These are all NON-array formulas (in sections for readability)

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

If there may be blanks in the range
C1: =INDEX(A1:A10,INDEX(MATCH(SMALL(INDEX(COUNTIF(A1:A 10,"<"&A1:A10)+
(A1:A10="")*10^99,0),B1),INDEX(COUNTIF(A1:A10,"<"& A1:A10)+
(A1:A10="")*10^99,0),0),0))

If there may be blanks and numbers in the range
C1: =INDEX(A1:A10,MATCH(SMALL(INDEX(COUNTIF(A1:A10,"<" &A1:A10&"")+
ISTEXT(A1:A10)*COUNT(A1:A10)+(A1:A10="")*10^99,0), B1),INDEX(
COUNTIF(A1:A10,"<"&A1:A10&"")+ISTEXT(A1:A10)*COUNT (A1:A10)+
(A1:A10="")*10^99,0),0))

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"FiluDlidu" wrote in message
...
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?

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