Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Another sort of ranking...
My problem...
I have the Rank( ) function But following (ascending order) I numbers: sais: need: 4 5 3 4 5 3 1 1 1 1 1 1 1 1 1 3 4 2 Any solution? -- Thanks, Ciprian |
#2
|
|||
|
|||
If this is a matter of frequent need using the same values each time, one
option is to got o ToolsOptionsCustom Lists and create a list in the order you want the items sorted. From then on you can use DataSortOptions to specify that custom list as the sort key. HTH |:) "Ciprian Baciu" wrote: My problem... I have the Rank( ) function But following (ascending order) I numbers: sais: need: 4 5 3 4 5 3 1 1 1 1 1 1 1 1 1 3 4 2 Any solution? -- Thanks, Ciprian |
#3
|
|||
|
|||
Another play to try ..
Adapting from a post by Daniel M. ( http://tinyurl.com/8snkd ) Assuming the source numbers are in A1:A6 Put in the formula bar for B1, and array-enter (press CTRL+SHIFT+ENTER): =RANK(A1,$A$1:$A$6,1)-(COUNTIF($A$1:$A$6,"<"&A1)-SUM((1/COUNTIF($A$1:$A$6,$A $1:$A$6))*($A$1:$A$6<A1))) Copy B1 down to B6 This seems to return the desired ranking in B1:B6, viz. you'd get in A1:B6: 4 3 4 3 1 1 1 1 1 1 3 2 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Ciprian Baciu" wrote in message ... My problem... I have the Rank( ) function But following (ascending order) I numbers: sais: need: 4 5 3 4 5 3 1 1 1 1 1 1 1 1 1 3 4 2 Any solution? -- Thanks, Ciprian |
#4
|
|||
|
|||
Thank you, Max.
The formula really works (at least on a few source numbers), but on 4000 rows I have waited 10 minutes and then CTRL+ALT+DEL seemed to be the only solution that could give me back my computer ;). -- Thanks, Ciprian "Max" wrote: Another play to try .. Adapting from a post by Daniel M. ( http://tinyurl.com/8snkd ) Assuming the source numbers are in A1:A6 Put in the formula bar for B1, and array-enter (press CTRL+SHIFT+ENTER): =RANK(A1,$A$1:$A$6,1)-(COUNTIF($A$1:$A$6,"<"&A1)-SUM((1/COUNTIF($A$1:$A$6,$A $1:$A$6))*($A$1:$A$6<A1))) Copy B1 down to B6 This seems to return the desired ranking in B1:B6, viz. you'd get in A1:B6: 4 3 4 3 1 1 1 1 1 1 3 2 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Ciprian Baciu" wrote in message ... My problem... I have the Rank( ) function But following (ascending order) I numbers: sais: need: 4 5 3 4 5 3 1 1 1 1 1 1 1 1 1 3 4 2 Any solution? -- Thanks, Ciprian |
#5
|
|||
|
|||
You're welcome ! Do hang around this thread awhile for thoughts /
alternatives from others that might be thrown this way .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Ciprian Baciu" wrote in message ... Thank you, Max. The formula really works (at least on a few source numbers), but on 4000 rows I have waited 10 minutes and then CTRL+ALT+DEL seemed to be the only solution that could give me back my computer ;). -- Thanks, Ciprian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sort by row instead of by column? | Excel Worksheet Functions | |||
sorting more than 3 keys | Excel Discussion (Misc queries) | |||
How do I sort a column of data and have each data row sort accordi | Excel Discussion (Misc queries) | |||
"-" ignored in sort | Excel Discussion (Misc queries) | |||
Data > Sort function amnesia? | Excel Discussion (Misc queries) |