Excel 2007 : Problem with repeated value in RANK formula

Hi:

I am trying to rank the following in descending order with name being in column A and value in column B:

ACC -3
AMBUJACEM -20.5
ASIANPAINT -18
AXISBANK 21.5
BAJAJ-AUTO -26
BANKBARODA -7.5
BHARTIARTL 5.5
BHEL 17
BPCL 30.5
CAIRN -26
CIPLA 9.5
COALINDIA 2.5
DLF 25
DRREDDY 30.5
GAIL -9.5
GRASIM -3
HCLTECH -0.5
HDFC 15
HDFCBANK 1.5
HEROMOTOCO -17
HINDALCO 22
HINDUNILVR -12
ICICIBANK 1
IDFC -1
INFY -14.5
ITC -11.5
JINDALSTEL 0.5
JPASSOCIAT 7.5
KOTAKBANK 0
LT -19.5
M&M 11
MARUTI 10
NTPC 4
ONGC -19
PNB 24
POWERGRID 0
RANBAXY 0
RELIANCE -6
RELINFRA -11.5
SAIL 12
SBIN 9
SESAGOA 8.5
SIEMENS -8.5
STER 5.5
SUNPHARMA -20.5
TATAMOTORS 13.5
TATAPOWER -6
TATASTEEL -7.5
TCS 30.5
WIPRO -23.5


I am using =RANK(B1,$B:$B,0) but I get an error when the rank value is repeated. In the above example BPCL, DRREDDY & TCS all have 30.5

Can somone help me with the formula?

Thanks & Regards,

I don't get an error... I just get repeated rank number...

Can you post a sample file showing the error occurring?

Why not do sort A1:B50 by column B?

ok... i don't now what "error" is that but here's a try....
ranking with "duplicates" i hope this is what you want. if not please tell us what is the expected outcome...

=RANK(B1,$B$1:$B$50,0)+COUNTIF($B$1:$B1,B1)-1

Thanks a lot vlady.

This is exactly what I wanted...

You guys make things look so simple and easy....

Thanks always....