1. 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?

2. [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

3. 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

4. =RANK(C4,\$c\$4:\$c\$8,1)

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

Steve

5. Aaaahhhhh

It was the third parameter I was omitting, thanks Steve

6. [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]

=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

7. Thanks Franciz, I appreciate your time

8. [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)

Posting Permissions

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