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?

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?

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"LiAD" wrote:

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?

Luke,

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

HTH,
Bernie
MS Excel MVP


"Luke M" wrote in message
...
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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"LiAD" wrote:

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,

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Bernie Deitrick" wrote:

Luke,

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

HTH,
Bernie
MS Excel MVP


"Luke M" wrote in message
...
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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"LiAD" wrote:

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?

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

Luke M
*Remember to click "yes" if this post helped you!*


"Bernie Deitrick" wrote:

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?

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

Thanks a lot for your help

"Luke M" wrote:

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

Luke M
*Remember to click "yes" if this post helped you!*


"Bernie Deitrick" wrote:

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?