Use the RANK and COUNTIF combination to get the unique ranking

Dear all,

I used the RANK and COUNTIF combination to get the unique ranking of a range. For example, the formula is: =RANK(A1,$A$1:$A$890,0)+COUNTIF($A$1:A1,A1)-1

To my surprise, the failed to produce the results that I want. I attached a sample file here. Basically, the two ranks of 416 and 418 are not there. My frustrated


A source below really shows the formula should work.

http://www.excelforum.com/excel-gene...-formulas.html

Hi daddylonglegs,

I very appreciate your great help for this problem. Yes, it really looks weird that the RANK() function produces different numbers, although they are the same number! Maybe this is due the number of decimal places that the RANK() function uses?

Anyway, your formula using both COUNTIF worked perfectly! Thank you.

Originally Posted by billj

Hi daddylonglegs,

I very appreciate your great help for this problem. Yes, it really looks weird that the RANK() function produces different numbers, although they are the same number! Maybe this is due the number of decimal places that the RANK() function uses?

Anyway, your formula using both COUNTIF worked perfectly! Thank you.

Had exactly the same problem and your COUNTIF worked for me too.!
Many Thanks