rank from low to high

[Guest]

I've got a great formula to rank to rank race car drivers and their points;
{INDEX($C$4:$C$39,MATCH(ROW()-ROW($E$4)+1,RANK($D$4:$D$39,$D$4:$D$39)+COUNTIF(OFFSET($D$4:$D$39,,,ROW($D$4:$D$39)-ROW($D$4)+1,1),$D$4:$D$39)-1,0))}
, but I need it to rank from low to high. Is their a way to modifiy the above
formula to do this?
C4:C39 is drivers names
D434 is points
D434 is results
I tried adjusting the RANK part of the formula but it dosent work.
Thanks in advance.

 

[Trevor Shuttleworth]

From the Excel Help:

Syntax

RANK(number,ref,order)

Number is the number whose rank you want to find.

Ref is an array of, or a reference to, a list of numbers. Nonnumeric
values in ref are ignored.

Order is a number specifying how to rank number.

a.. If order is 0 (zero) or omitted, Microsoft Excel ranks number as if
ref were a list sorted in descending order.
b.. If order is any nonzero value, Microsoft Excel ranks number as if ref
were a list sorted in ascending order.
Try changing: RANK($D$4:$D$39,$D$4:$D$39)
to: RANK($D$4:$D$39,$D$4:$D$39,1)

So:

{=INDEX($C$4:$C$39,MATCH(ROW()-ROW($E$4)+1,RANK($D$4:$D$39,$D$4:$D$39,1)+COUNTIF(OFFSET($D$4:$D$39,,,ROW($D$4:$D$39)-ROW($D$4)+1,1),$D$4:$D$39)-1,0))}

Seems to work OK for me

Regards

Trevor

 

[Guest]

Found my own mistake but thanks for being there

 

[Guest]

Yes that did work I must have entered as a non array formula Thanks

 

[Trevor Shuttleworth]

Good. The answer is always obvious once you know what it is. A bit like,
if you've lost something, you always find it in the last place you look
;-)