Rank Duplicate values

[Clare1805]

I have a list of values (some are duplicates) which I need to rank.

The problem arises where I have more than one of any rank, the next rank skips a number. I need to have a list of sequential numbers and therefore can't have any numbers missing.

Example:
Value Rank Desired Rank
120 1 1
125 2 2
130 3 3
130 3 3
142 5 4

Any ideas?

 

[anaBonzi]

this formula not working, are their any reason or tips

DataSheet= SampleSheet
1 2 3 4 5 6 7 8 9 10 11 <tbody bgcolor="#ffffff"> </tbody>	B C D E Value Rank Desired Answer 120 1 1 1 125 3 2 2 130 5 3 3 130 5 3 3 <colgroup> <col width="53"> <col width="50"> <col width="69"> <col width="63"> </colgroup><tbody bgcolor="#ffffff"> </tbody> 142 9 4 4 120 1 1 1 125 3 2 2 130 5 3 3 130 5 3 3 142 9 4 4 <colgroup> <col width="53"> <col width="50"> <col width="69"> <col width="63"> </colgroup><tbody bgcolor="#ffffff"> </tbody>

<tbody bgcolor="#ffffff">
</tbody>

<colspan=12 rowspan="5" height="90" width="12"> </colspan=12>

Used Formula ...(With Running MicrosoftExcel Ver 2003)
No	Addr'	If use below Formula, You'll Get Result as Right	Result	Etc
1	C2	=RANK(B2,$B$2:$B$11,-1)	1
2		C2 His Formula Used This Cell -> C2:C11
3	E2	=SUMPRODUCT(($B$2:$B$11< =B2)*(MATCH($B$2:$B$11,$B$2:$B$11,0)=(ROW($B$2:$B$11)-ROW($B$2)+1)))	1
4		E2 His Formula Used This Cell -> E2:E11

<tbody>
</tbody>