Ranking Results

  • Thread starter Thread starter littleredhairedgirl
  • Start date Start date
L

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
 
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).
 
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
 
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
 
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
 
Back
Top