Ranking Without Skipping Ranks When a Tie Occurs

[sirkevinthegeek]

Hi, I'm having trouble with the RANK function. I need to find a formula that
will give me sequential ranking for a range of values without skipping ranks
after a duplicate occurs. It needs to return 1 for the LARGEST value in the
range.{20,30,30,40,40,50} should get ranked as {4,3,3,2,2,1}. I found this
in another post:

SUMPRODUCT(--(results1[[#This Row],[Total Weight]]>[Total
Weight]),1/COUNTIF([Total Weight],[Total Weight]))+1

This almost works, but it returns a rank of 1 for the lowest value in the
range instead of the highest. Any help would be greatly appreciated.

 

[sirkevinthegeek]

Oops. The formula that Biff provided in another post was actually:

SUMPRODUCT(--(A2>A$2:A$6),1/COUNTIF(A$2:A$6,A$2:A$6&""))+1

.... the one below has my crazy references in it. Feel free to reply with a
formula like the one above instead.

 

[T. Valko]

It needs to return 1 for the LARGEST value

Just replace the greater than operator (>) with less than operator (<).

--
Biff
Microsoft Excel MVP

Oops. The formula that Biff provided in another post was actually:

SUMPRODUCT(--(A2>A$2:A$6),1/COUNTIF(A$2:A$6,A$2:A$6&""))+1

... the one below has my crazy references in it. Feel free to reply with
a
formula like the one above instead.

Hi, I'm having trouble with the RANK function. I need to find a formula
that
will give me sequential ranking for a range of values without skipping
ranks
after a duplicate occurs. It needs to return 1 for the LARGEST value in
the
range.{20,30,30,40,40,50} should get ranked as {4,3,3,2,2,1}. I found
this
in another post:

SUMPRODUCT(--(results1[[#This Row],[Total Weight]]>[Total
Weight]),1/COUNTIF([Total Weight],[Total Weight]))+1

This almost works, but it returns a rank of 1 for the lowest value in the
range instead of the highest. Any help would be greatly appreciated.

 

[sirkevinthegeek]

I could've sworn I had tried that, but voila. You're the man, Biff.

It needs to return 1 for the LARGEST value

Just replace the greater than operator (>) with less than operator (<).

--
Biff
Microsoft Excel MVP

Oops. The formula that Biff provided in another post was actually:

SUMPRODUCT(--(A2>A$2:A$6),1/COUNTIF(A$2:A$6,A$2:A$6&""))+1

... the one below has my crazy references in it. Feel free to reply with
a
formula like the one above instead.

Hi, I'm having trouble with the RANK function. I need to find a formula
that
will give me sequential ranking for a range of values without skipping
ranks
after a duplicate occurs. It needs to return 1 for the LARGEST value in
the
range.{20,30,30,40,40,50} should get ranked as {4,3,3,2,2,1}. I found
this
in another post:

SUMPRODUCT(--(results1[[#This Row],[Total Weight]]>[Total
Weight]),1/COUNTIF([Total Weight],[Total Weight]))+1

This almost works, but it returns a rank of 1 for the lowest value in the
range instead of the highest. Any help would be greatly appreciated.

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

I could've sworn I had tried that, but voila. You're the man, Biff.

It needs to return 1 for the LARGEST value

Just replace the greater than operator (>) with less than operator (<).

--
Biff
Microsoft Excel MVP

Oops. The formula that Biff provided in another post was actually:

SUMPRODUCT(--(A2>A$2:A$6),1/COUNTIF(A$2:A$6,A$2:A$6&""))+1

... the one below has my crazy references in it. Feel free to reply
with
a
formula like the one above instead.

:

Hi, I'm having trouble with the RANK function. I need to find a
formula
that
will give me sequential ranking for a range of values without skipping
ranks
after a duplicate occurs. It needs to return 1 for the LARGEST value
in
the
range.{20,30,30,40,40,50} should get ranked as {4,3,3,2,2,1}. I found
this
in another post:

SUMPRODUCT(--(results1[[#This Row],[Total Weight]]>[Total
Weight]),1/COUNTIF([Total Weight],[Total Weight]))+1

This almost works, but it returns a rank of 1 for the lowest value in
the
range instead of the highest. Any help would be greatly appreciated.