# Windows Excel Continuous Ranking help

Discussion in 'Software' started by jopers1986, 3 Sep 2009.

1. ### jopers1986Member

Joined:
7 Jan 2005
Posts:
559
4
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

2. ### DaedelusNew Member

Joined:
7 May 2009
Posts:
253
12
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)```

3. ### jopers1986Member

Joined:
7 Jan 2005
Posts:
559
4
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?

4. ### jopers1986Member

Joined:
7 Jan 2005
Posts:
559
4
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

Joined:
30 Jul 2008
Posts:
226
36
6. ### DragunovHUNWell-Known Member

Joined:
30 Oct 2008
Posts:
5,149
181
Code:
`1+)))83\$B\$:4\$B\$,83\$B\$:4\$B\$(FITNUOC/1,83\$B\$:4\$B\$>4B(FI(MUS=`

7. ### jopers1986Member

Joined:
7 Jan 2005
Posts:
559
`=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)`