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.

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

SUMPRODUCT(--(A2A$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.

"sirkevinthegeek" wrote:

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.

It needs to return 1 for the LARGEST value

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

--
Biff
Microsoft Excel MVP


"sirkevinthegeek" wrote in
message ...
Oops. The formula that Biff provided in another post was actually:

SUMPRODUCT(--(A2A$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.

"sirkevinthegeek" wrote:

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.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"sirkevinthegeek" wrote in
message ...
I could've sworn I had tried that, but voila. You're the man, Biff.

"T. Valko" wrote:

It needs to return 1 for the LARGEST value

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

--
Biff
Microsoft Excel MVP


"sirkevinthegeek" wrote in
message ...
Oops. The formula that Biff provided in another post was actually:

SUMPRODUCT(--(A2A$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.

"sirkevinthegeek" wrote:

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.