Another sort of ranking...

C

Ciprian Baciu

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?
 
C

CyberTaz

If this is a matter of frequent need using the same values each time, one
option is to got o Tools>Options>Custom Lists and create a list in the order
you want the items sorted. From then on you can use Data>Sort>Options to
specify that custom list as the sort key.

HTH |:>)
 
M

Max

Another play to try ..

Adapting from a post by Daniel M.
(re: 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
 
C

Ciprian Baciu

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 ;).
 
M

Max

You're welcome ! Do hang around this thread awhile for thoughts /
alternatives from others that might be thrown this way ..
 
Top