SMALL/LARGE and text

  • Thread starter Thread starter FiluDlidu
  • Start date Start date
F

FiluDlidu

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 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?
Click to expand...

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$10),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.
 
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:A10,"<"&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)
 
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.
 
Back
Top