Instead of

=RANK(B1;$B$1:$B$5)

use

=COUNT($C$1:$C$5)-(RANK(C1;$C$1:$C$5,0)+COUNTIF($C$1:C1;C1))+2

HTH,
Bernie
MS Excel MVP


"LiAD" wrote in message
...
Afternoon,

I have data such which i would like to rank and then order the ranked data
in highest to lowest, all automatically so it re-orders when data changes.

My technique so far was maybe slightly complicated

Example of data in col A and B

a 1
b 2
c 3
d 4
e 1

I then use =RANK(B1;$B$1:$B$5) and will it ouput in col C (written left to
right instead of top to bottom), 4,3,2,1,4

In col D I put the order of the data I want, highest at the top so it reads
top to bottom 1,2,3,4,5

In col E I use =INDEX($B$1:$B$5;MATCH(D1;$C$1:$C$5;0)) so that excel finds
the number from col B that is associated with the highest ranked position.
Excel will return 4,3,2,1,n/a

How can I avoid the n/a problem when I have more than one set of data of
equal ranking but keep it automatic?

Any ideas?