Auto Ranking a 2x2 table with equal values

[LiAD]

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?

 

[Bernie Deitrick]

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

 

[Luke M]

In column C
=LARGE($B$1:$B$5,ROW())

Copy down as far as you want. FYI, if you want to sort the other way, use
SMALL function.

 

[Bernie Deitrick]

Luke,

Your use of LARGE has the same limitations as the RANK approach when dealing with ties.

HTH,
Bernie
MS Excel MVP

 

[Luke M]

Bernie,

I must disagree. The rank approach simply stated what rank the number was,
there the LARGE function returns the actual number. Rank approach was causing
errors because OP was using INDEX to retrace and find his numbers, and could
not find duplicates.

I have recreated OP's scenario, and my formula works correctly.

 

[Luke M]

Further clarification, my formula eliminated the need for the second helper
column. Testing your formula, I see what you were thinking of.

 

[LiAD]

Works fine with the countif. It actually inverts the order but thats easy to
sort.

Thanks a lot for your help