descending order of a series of data

[kent]

I have 2 coulumns of data,
A is the score, and B is the ranking in descending order of A
WHat should be the formula in column B oplease?


A B
76 4
88 2
99 1

85 3
24 5

 

[T. Valko]

Try this...

=IF(A2="","",RANK(A2,A$2:A$7))

 

[Bernd P]

Hello Kent,

Do not use RANK, use instead:
=COUNTIF($A$1:$A$99,">"&A1)+COUNTIF(A$1:A1,A1)
and copy down.
If identical values should get identical ranks, substitute the second
term by 1.

This formula can deal with number AND with strings. RANK is an
obsolete function, I suggest.

Regards,
Bernd

 

[T. Valko]

Do not use RANK

Kent, do not listen to Bernd.

 

[Bernd P]

Hello Biff,

I know that you know it - but you do not tell others...

Try to rank strings with RANK or to return unique values for identical
inputs:
http://sulprobil.com/html/rank.html

Regards,
Bernd

 

[T. Valko]

Instead of saying:

Do not use RANK

Why don't you say something like:

Here's an alternative if you want to do this or this.

 

[Teethless mama]

RANK is an obsolete function, I suggest.

Not so fast...

You can use RANK()+COUNTIF()-1. This formula is few characters shorter and
more elegant than your formula

=RANK(A1,$A$1:$A$99)+COUNTIF($A$1:A1,A1)-1

is the same result as:

 

[Bernd P]

Hello Biff,

...

Why don't you say something like:

Here's an alternative if you want to do this or this.
...

Because I do not use RANK anymore.

But surely we both can ask the OP next time what he needs to get in
case of identical keys.

(1) If he is happy with identical ranks then its RANK for him,
(2) if he needs unique ranks he can use Teethless' RANK + COUNTIF -1,
(3) if its no longer numbers but strings he can use COUNTIF + COUNTIF,
(4) and if there are more than one rank/sort levels then it might be
SUMPRODUCT.

COUNTIF + COUNTIF is not the fastest solution for (1) and (2) but it
kills the first three birds (1)(2)(3) with one stone:
http://sulprobil.com/html/rank.html

Regards,
Bernd

 

[Kent]

All your formulae fit my use, thank you Teethless, Valko & Bernd.