SMALL/LARGE and text
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?
|