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

Kent

Try this...

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

--
Biff
Microsoft Excel MVP


"kent" wrote in message
...
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
---------------------

Kent

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

Do not use RANK

Kent, do not listen to Bernd.

--
Biff
Microsoft Excel MVP


"Bernd P" wrote in message
...
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

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

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.

--
Biff
Microsoft Excel MVP


"Bernd P" wrote in message
...
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

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:

=COUNTIF($A$1:$A$99,""&A1)+COUNTIF(A$1:A1,A1)


"Bernd P" wrote:

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
.

Hello Biff,

...
Do not use RANK

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

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




"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:

=COUNTIF($A$1:$A$99,""&A1)+COUNTIF(A$1:A1,A1)


"Bernd P" wrote:

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
.