Question regarding "RANK" function or similar...

Posted by Chris Rock on November 26, 2001 11:52 AM

Is there a way to supplement the RANK function so that when you have 2 values that are the same RANK, the second one is RANKED one lower? (perhaps based on Alphabetic criteria?)

For Example:

Name.....HR's.....RANK
----------------------
Bill ....40HR.....1
Chris....40HR.....2 &LT;=====This would NORMALLY be "1"
Bob ....35HR.....3

I appreciate any help with this.
Thanks.

Posted by Aladin Akyurek on November 26, 2001 12:53 PM

Chris --

Assuming that B1:B3 houses the hours (without the particle HR),

=RANK(B1,B$1:B$3)

copied till B3 would give:

1
1
3

So, what do you mean by

Chris....40HR.....2 &LT;=====This would NORMALLY be "1" ?

Care to elaborate?

Aladin

===========

Posted by Chris Rock on November 26, 2001 1:15 PM

I used this formula, (probably in error) in B1 to B3:
=Rank(B:B,B:B) and got the results, 1, 1, 3 in B1 to B3.

Posted by Chris Rock on November 26, 2001 1:17 PM

OOPS - I meant C1 to C3.

Posted by Russell Hauf on November 26, 2001 1:20 PM

He wants UNIQUE values...

Chris - Straight from Microsoft:

(also as a link below)

http://support.microsoft.com/support/kb/articles/Q152/5/67.asp

Note that I was not able to get the first formula that they use to work, but the second, longer formula did work.

HTH,

Russell

Posted by Chris Rock on November 26, 2001 1:23 PM

Re: Question regarding

Jeez, I am stupid. I should have read your post fully before replying.

Yes, the results are:
1
1
3

I WOULD LIKE the results to read:
1
2
3

The problem is I am lacking a "2" - or any time you use the RANK function and 2 values have the same RANK, it uses the same RANK for each instance, and then skips down to whatever number would be next. I would like to, if possible, have the formula go ahead one number if the two values were of the same RANK.

Posted by Chris Rock on November 26, 2001 1:33 PM

Re: He wants UNIQUE values...

Thanks, Russell,
This should help. I got the Array formula to work, so I am glad.

Posted by Aladin Akyurek on November 26, 2001 1:43 PM

Re: Question regarding

If you want to avoid assigning the same rank to equal values, use:

=RANK(B1,B$1:B$100)+COUNTIF(B$1:B1,B1)-1

Aladin

=========== Jeez, I am stupid. I should have read your post fully before replying. Yes, the results are: