Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SMALL/LARGE and text
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SMALL/LARGE and text
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SMALL/LARGE and text
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SMALL/LARGE and text
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Charting small and large percentages | Charts and Charting in Excel | |||
Sorting numbers in a row from small to large | Excel Discussion (Misc queries) | |||
Compound Lookup, Large/Small, Match... | Excel Worksheet Functions | |||
SMALL and LARGE | Excel Discussion (Misc queries) | |||
Attaching labels when using large or small functions | Excel Worksheet Functions |