Results 1 to 8 of 8

Thread: Reverse Rank

  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    I have looked around the interweb for this but don't quite get the syntax.

    In column C, starting at C4 I have a series of numbers (decimalised times) that are race positions.

    A subset of the numbers are:

    37.15
    36.46
    36.20
    34.28
    34.01

    Unlike the RANK function the lowest number is actually first what is the formula to get the reverse rank?
    Jerry

  2. #2
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Jezza' post='766587' date='22-Mar-2009 00:56']I have looked around the interweb for this but don't quite get the syntax.

    In column C, starting at C4 I have a series of numbers (decimalised times) that are race positions.

    A subset of the numbers are:

    37.15
    36.46
    36.20
    34.28
    34.01

    Unlike the RANK function the lowest number is actually first what is the formula to get the reverse rank?[/quote]

    Hi Jezza

    try this array formula, confirm by Ctrl,Shift and Enter

    =SUM(1*(C4<$C$4:$C$8))+1+IF(ROW(C4)-ROW($C$4)
    =0,0,SUM(1*(C4=OFFSET($C$4,0,0,INDEX(ROW(C4)-ROW(
    C$4)+1,1)-1,1))))

    Hope this help
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Nope, it still puts the highest value as first and lowest last.

    These are times so I want it so the fastest time (lowest number) is first
    Jerry

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    =RANK(C4,$c$4:$c$8,1)

    Any number other than 0 in the optional "order" parameter will do...

    Steve

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Aaaahhhhh

    It was the third parameter I was omitting, thanks Steve
    Jerry

  6. #6
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Jezza' post='766596' date='22-Mar-2009 02:12']Nope, it still puts the highest value as first and lowest last.

    These are times so I want it so the fastest time (lowest number) is first[/quote]

    What about this

    =RANK(C4,$C$4:$C$8,1)

    it will give you the following :

    37.15 5
    36.46 4
    36.2 3
    34.28 2
    34.01 1
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks Franciz, I appreciate your time
    Jerry

  8. #8
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Jezza' post='766604' date='22-Mar-2009 02:25']Thanks Franciz, I appreciate your time [/quote]

    I didn't realised that you are referring to an ascending order ranking
    Glad that you find a solution

    btw, for the benefit of others, it is not necesary to use an array formula which
    I have posted earlier for a descending order rank,
    a much more simplify and non array formula would be

    =RANK(C4,$C$4:$C$8)
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •