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

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

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

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

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