Ranking Results

[littleredhairedgirl]

I have a list of numbers. Each row has a number representing
investment, followed by a number representing a return. I then
computer a percent of return as a percentage.

There is a group of these over several rows. I'd like to rank each
result, highest to lowest, without sorting.

So...
Investment Return Percent Rank
22 33 50.00% 4
6 8 33.33% 6
27 38 40.74% 5
18 38 111.11% 1
27 49 81.48% 2
21 28 33.33% 7
16 24 50.00% 3

I have this on
http://spreadsheets.google.com/ccc?key=0Av_hLM5UdQ4KdFlRRmdjSkdmbFVwV0ZCR1pELUxBYWc&hl=en

 

[Bob Phillips]

Try

=RANK(C1,$C$1:$C$7)+COUNTIF($C1:$C$7,C1)-1

 

[Pete_UK]

Use a formula like this in D2:

=RANK(C2,C$2:C$8)

then copy down.

Hope this helps.

Pete

 

[Gary''s Student]

In D1 enter:
=RANK(C1,C$1:C$7) and copy down
In E1 enter:
=D1
In E2 enter:
=IF(COUNTIF($E$1:E1,D2)>0,D2+1,D2) and copy down.

We see:

22 33 50.00% 3 3
6 8 33.33% 6 6
27 38 40.74% 5 5
18 38 111.11% 1 1
27 49 81.48% 2 2
21 28 33.33% 6 7
16 24 50.00% 3 4


Column D establishes the rank and column E fixes the ties (duplicates).

 

[littleredhairedgirl]

Thanks, That all helped get me going toward the right direction. I
still wanted to rank according to the percentage in Column I.

I would break the ties (duplicates) by the lowest Investment

 

[Bob Phillips]

Mine did that for you, although it said column C not I.

--
__________________________________
HTH

Bob

Thanks, That all helped get me going toward the right direction. I
still wanted to rank according to the percentage in Column I.

I would break the ties (duplicates) by the lowest Investment

 

[Bernd P]

Hello Bob,

Sorry, no. Your formula does not handle duplicates and a secondary
sorting criterion (just dupes).

I suggest to solve this with the more complex example (the lower one
with SUMPRODUCT) from:
http://www.sulprobil.com/html/sorting.html

This would even work with text, not only numbers.

Regards,
Bernd