View Full Version : rank problems
CCkfm2000
07-31-2006, 06:37 AM
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
07-31-2006, 07:15 AM
What is wrong with the formula you have now?
CCkfm2000
07-31-2006, 07:23 AM
i just need to rank the unique numbers not the dublicateones.
Shazam
07-31-2006, 07:46 AM
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
07-31-2006, 08:19 AM
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
07-31-2006, 08:23 AM
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
07-31-2006, 08:28 AM
move down to cell o432
it's quite a big spreadsheet
Shazam
07-31-2006, 08:45 AM
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)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.