| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting array function explanation please
I searched previous posts for a formula that could sort a column of
text values and found this gem, which I have adapted to sort a range named AList. The original formula was supplied by Harlan Grove in Jun 2004... =INDEX(AList,MATCH(SMALL(COUNTIF(AList,"<"&AList)+ COUNT(AList),ROW()- ROW($E$1)+1),COUNTIF(AList,"<"&AList)+COUNT(AList) ,0)) This array formula works perfectly; however, I am having trouble understanding how it works. The SMALL functions first argument, COUNTIF(AList,"<"&AList) +COUNT(AList), has to return an array. This is the bit I cant understand. Can anyone explain for me how COUNTIF(AList,"<"&AList)+COUNT(AList) manages to return an array for the SMALL functions first argument? Ken Johnson |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sorting array function explanation please
Ken Johnson wrote:
I searched previous posts for a formula that could sort a column of text values and found this gem, which I have adapted to sort a range named AList. The original formula was supplied by Harlan Grove in Jun 2004... =INDEX(AList,MATCH(SMALL(COUNTIF(AList,"<"&AList)+ COUNT(AList),ROW()- ROW($E$1)+1),COUNTIF(AList,"<"&AList)+COUNT(AList) ,0)) This array formula works perfectly; however, I am having trouble understanding how it works. The SMALL functions first argument, COUNTIF(AList,"<"&AList) +COUNT(AList), has to return an array. This is the bit I cant understand. Can anyone explain for me how COUNTIF(AList,"<"&AList)+COUNT(AList) manages to return an array for the SMALL functions first argument? Ken Johnson Hi Ken, Your confusion is justified since COUNTIF and COUNT can only return scalars. However I'm betting, since this is an array formula, an array of scalars is created here. That is the nature of array formulae. You can test this by placing the cursor on a cell with the formula and using the formula evaluator under Tools | Formula Auditing | Evaluate Formula. Try it on a fairly small sample (3-4 rows) of data. FWIW SMALL is just as happy to accept a single constant for its first argument, even though it seems pointless to do so: =SMALL(5,1) returns 5 Cool find, by the way! Hope this helps. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sorting array function explanation please
Hi smartin,
Thanks for your suggestion! I've not used the Formula evaluator before. It clearly shows the arrays of values that the formula uses. Perfect! Cool find, by the way! Cooler still, in the same post Harlan also supplied a version that will sort a mixture of text and numbers... =INDEX(AList,MATCH(SMALL(COUNTIF(AList,"<"&AList) +COUNT(AList)*ISTEXT(AList),ROW()-ROW($E$1)+1),COUNTIF(AList,"<"&AList) +COUNT(AList)*ISTEXT(AList),0)) where the formula starts in E1. Also, just swapping the "<"s with ""s changes the sort to descending. Thanks again for your help. Ken Johnson smartin wrote: Ken Johnson wrote: I searched previous posts for a formula that could sort a column of text values and found this gem, which I have adapted to sort a range named AList. The original formula was supplied by Harlan Grove in Jun 2004... =INDEX(AList,MATCH(SMALL(COUNTIF(AList,"<"&AList)+ COUNT(AList),ROW()- ROW($E$1)+1),COUNTIF(AList,"<"&AList)+COUNT(AList) ,0)) This array formula works perfectly; however, I am having trouble understanding how it works. The SMALL function's first argument, COUNTIF(AList,"<"&AList) +COUNT(AList), has to return an array. This is the bit I can't understand. Can anyone explain for me how COUNTIF(AList,"<"&AList)+COUNT(AList) manages to return an array for the SMALL function�s first argument? Ken Johnson Hi Ken, Your confusion is justified since COUNTIF and COUNT can only return scalars. However I'm betting, since this is an array formula, an array of scalars is created here. That is the nature of array formulae. You can test this by placing the cursor on a cell with the formula and using the formula evaluator under Tools | Formula Auditing | Evaluate Formula. Try it on a fairly small sample (3-4 rows) of data. FWIW SMALL is just as happy to accept a single constant for its first argument, even though it seems pointless to do so: =SMALL(5,1) returns 5 Cool find, by the way! Hope this helps. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sorting array function explanation please
Your confusion is justified since COUNTIF and COUNT can only return
scalars. However I'm betting, since this is an array formula, an array of scalars is created here. That is the nature of array formulae. That is correct. COUNTIF(ALIST,"<"&ALIST)+COUNT(AList) If AList refers to A1:A5 then this is how the array breaks down: COUNTIF(A1:A5,"<"&A1)+COUNT(A1:A5) COUNTIF(A1:A5,"<"&A2)+COUNT(A1:A5) COUNTIF(A1:A5,"<"&A3)+COUNT(A1:A5) COUNTIF(A1:A5,"<"&A4)+COUNT(A1:A5) COUNTIF(A1:A5,"<"&A5)+COUNT(A1:A5) -- Biff Microsoft Excel MVP "smartin" wrote in message ... Ken Johnson wrote: I searched previous posts for a formula that could sort a column of text values and found this gem, which I have adapted to sort a range named AList. The original formula was supplied by Harlan Grove in Jun 2004... =INDEX(AList,MATCH(SMALL(COUNTIF(AList,"<"&AList)+ COUNT(AList),ROW()- ROW($E$1)+1),COUNTIF(AList,"<"&AList)+COUNT(AList) ,0)) This array formula works perfectly; however, I am having trouble understanding how it works. The SMALL functions first argument, COUNTIF(AList,"<"&AList) +COUNT(AList), has to return an array. This is the bit I cant understand. Can anyone explain for me how COUNTIF(AList,"<"&AList)+COUNT(AList) manages to return an array for the SMALL functions first argument? Ken Johnson Hi Ken, Your confusion is justified since COUNTIF and COUNT can only return scalars. However I'm betting, since this is an array formula, an array of scalars is created here. That is the nature of array formulae. You can test this by placing the cursor on a cell with the formula and using the formula evaluator under Tools | Formula Auditing | Evaluate Formula. Try it on a fairly small sample (3-4 rows) of data. FWIW SMALL is just as happy to accept a single constant for its first argument, even though it seems pointless to do so: =SMALL(5,1) returns 5 Cool find, by the way! Hope this helps. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sorting array function explanation please
On Aug 23, 12:15 pm, Ken Johnson wrote:
Hi smartin, Thanks for your suggestion! I've not used the Formula evaluator before. It clearly shows the arrays of values that the formula uses. Perfect! Cool find, by the way! Cooler still, in the same post Harlan also supplied a version that will sort a mixture of text and numbers... =INDEX(AList,MATCH(SMALL(COUNTIF(AList,"<"&AList) +COUNT(AList)*ISTEXT(AList),ROW()-ROW($E$1)+1),COUNTIF(AList,"<"&AList) +COUNT(AList)*ISTEXT(AList),0)) where the formula starts in E1. Also, just swapping the "<"s with ""s changes the sort to descending. Thanks again for your help. Ken Johnson smartin wrote: Ken Johnson wrote: I searched previous posts for a formula that could sort a column of text values and found this gem, which I have adapted to sort a range named AList. The original formula was supplied by Harlan Grove in Jun 2004... =INDEX(AList,MATCH(SMALL(COUNTIF(AList,"<"&AList)+ COUNT(AList),ROW()- ROW($E$1)+1),COUNTIF(AList,"<"&AList)+COUNT(AList) ,0)) This array formula works perfectly; however, I am having trouble understanding how it works. The SMALL function's first argument, COUNTIF(AList,"<"&AList) +COUNT(AList), has to return an array. This is the bit I can't understand. Can anyone explain for me how COUNTIF(AList,"<"&AList)+COUNT(AList) manages to return an array for the SMALL function s first argument? Ken Johnson Hi Ken, Your confusion is justified since COUNTIF and COUNT can only return scalars. However I'm betting, since this is an array formula, an array of scalars is created here. That is the nature of array formulae. You can test this by placing the cursor on a cell with the formula and using the formula evaluator under Tools | Formula Auditing | Evaluate Formula. Try it on a fairly small sample (3-4 rows) of data. FWIW SMALL is just as happy to accept a single constant for its first argument, even though it seems pointless to do so: =SMALL(5,1) returns 5 Cool find, by the way! Hope this helps. Tell a lie... You just change SMALL to LARGE and leave the "<"s alone to change to descending sort. Ken Johnson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Complex Array sorting issue | Excel Worksheet Functions | |||
| sorting array formula | Excel Worksheet Functions | |||
| Problems when sorting data containing array formulas | Excel Worksheet Functions | |||
| Sorting within an array | Excel Discussion (Misc queries) | |||
| Explanation of when & how to use ( ) { } : ; , ! etc? | New Users to Excel | |||
Linear Mode
