Hi, Need some help with an excel function. i need to rank a set of numbers, the highest needs to be 1 etc. However, there will be duplicated numbers, and the standard rank function tackels this by missing out numbers so if i had the following numbers: 10, 10, 10, 3, 5, 5, 1, 0 It would give the rankings 1, 1, 1, 4, 4, 6, 7, 8 And what i need would be 1, 1, 1, 2, 2, 3, 4, 5 I've found formulas that do the 1st and 2nd, and one that does what i need, but requires you to control shift enter, which i really don't wnat to do.... anyone know how i do this? Thanks

I'm not sure if this is really what you want but, anyway... 1. Put the numbers you need to sort in a column 2. Sort them on descending order 3. Put a 1 in the first cell at the top of the next column 4. Use the formula =IF(A2<A1,B1+1,B1) in the cell below it 5. Drag the formula down to the bottom of the column Code: A B 1 10 1 2 10 =IF(A2<A1,B1+1,B1) 3 10 =IF(A3<A2,B2+1,B2) 4 5 =IF(A4<A3,B3+1,B3) 5 5 =IF(A5<A4,B4+1,B4) 6 3 =IF(A6<A5,B5+1,B5) 7 1 =IF(A7<A6,B6+1,B6) 8 0 =IF(A8<A7,B7+1,B7)

haha, yeah i've got this running, but there is other attached data, so when you then sort by another field, that sorting all goes tits up... bit of a pain really! Thanks tho, any other ideas?

This works... Code: =SUM(IF(B4>$B$4:$B$38,1/COUNTIF($B$4:$B$38,$B$4:$B$38)))+1 BUT it gives a descending rank (highest number is ranked lowest, and i need it the other way around. IS there a formula i could run off the new rank to flip it around? OR to do what i want....? Thanks

Chortle chortle.... if only that worked, a mate at work gave me some code which fixed me up nicely: Code: =IF(C4="N/A",99,IF($C$4:$C$38>=C4,SUM(IF(FREQUENCY(IF(LEN(IF($C$4:$C$38>=C4,$C$4:$C$38,0))>0,MATCH(IF($C$4:$C$38>=C4,$C$4:$C$38,0),IF($C$4:$C$38>=C4,$C$4:$C$38,0),0),""),IF(LEN(IF($C$4:$C$38>=C4,$C$4:$C$38,0))>0,MATCH(IF($C$4:$C$38>=C4,$C$4:$C$38,0),IF($C$4:$C$38>=C4,$C$4:$C$38,0),0),""))>0,1)),0)-2)+IF(B5=MIN($B$4:$B$38),1,0)