descending order of a series of data

  • Thread starter Thread starter kent
  • Start date Start date
K

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
 
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
 
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:
 
Hello Biff,
...

Why don't you say something like:

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

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