1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Windows Excel Continuous Ranking help

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

  1. jopers1986

    jopers1986 Member

    Joined:
    7 Jan 2005
    Posts:
    559
    Likes Received:
    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. Daedelus

    Daedelus New Member

    Joined:
    7 May 2009
    Posts:
    253
    Likes Received:
    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. jopers1986

    jopers1986 Member

    Joined:
    7 Jan 2005
    Posts:
    559
    Likes Received:
    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. jopers1986

    jopers1986 Member

    Joined:
    7 Jan 2005
    Posts:
    559
    Likes Received:
    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
     
  5. Mach

    Mach New Member

    Joined:
    30 Jul 2008
    Posts:
    226
    Likes Received:
    36
  6. DragunovHUN

    DragunovHUN Well-Known Member

    Joined:
    30 Oct 2008
    Posts:
    5,149
    Likes Received:
    181
    Code:
    1+)))83$B$:4$B$,83$B$:4$B$(FITNUOC/1,83$B$:4$B$>4B(FI(MUS=
     
  7. jopers1986

    jopers1986 Member

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

Share This Page