rank problems

[CCkfm2000]

hi all....

i need to rank some numbers.

e.g.

number rank
1 0
3 3
2 2
4 4
4 0
4 0
2 0
1 1

i've got this formula so far

=RANK(A2,$A$2:$A$169)+COUNTIF($A$2:A2,A2)-1


please help :dunno

thanks
</IMG>

[Shazam]

What is wrong with the formula you have now?

[CCkfm2000]

i just need to rank the unique numbers not the dublicateones.

[Shazam]

Assuming your data starts in cell A2. Input formula in cell B2 and copy down.


=IF(SUMPRODUCT(--(C$1:C1=C1))=C1,"",SUMPRODUCT((A2<$A$2:$A$9)/COUNTIF($A$2:$A$9,$A$2:$A$9&""))+1)

[CCkfm2000]

sorry to be a pain,

i've attached a copy of my spreadsheet.

i've had to delete a lot of data to post it.

i've add the above formula but i'm lost with it.

[Shazam]

sorry to be a pain,

i've attached a copy of my spreadsheet.

i've had to delete a lot of data to post it.

i've add the above formula but i'm lost with it.



When I open up the file I dont see any data.

[CCkfm2000]

move down to cell o432

it's quite a big spreadsheet

[Shazam]

Input formula in cell O433 and copy down.



=IF(SUMPRODUCT(--(P$432:P432=P432))=P432,"",SUMPRODUCT((N433<N$433:N$1000)/COUNTIF(N$433:N$1000,N$433:N$1000&""))+1)