• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Rank gives me a unique rank for same values

H

homesickhurdle31

New Member
Values Rank

3.72222222222222000000000000000 5

3.27777777777778000000000000000 12

3.33333333333333000000000000000 11

3.77777777777778000000000000000 4

3.50000000000000000000000000000 8

3.05555555555555000000000000000 15

3.38888888888889000000000000000 10

3.83333333333333000000000000000 2

3.83333333333333000000000000000 2

3.94444444444444000000000000000 1

3.38888888888889000000000000000 9

3.05555555555556000000000000000 14

3.11111111111111000000000000000 13

3.61111111111111000000000000000 6

3.50000000000000000000000000000 7


Hi, I am trying to do a rank for a bunch of values that I have. The values that are given to me are shown on the left. I have done a =RANK() to it and I got the ranking values on the right.


Notice for 3.50000000 and 3.50000000 (despite being the same values) I get a unique rank for both (rank 7 and rank 8). I would like to find out what is causing this as what I understand is that the RANK function should give me the same rank for the same values.


Any help would be appreciated.


Thanks!
 
Hi, homesickhurdle31!


The values you're getting displayed are internally stored with their actual values and surely Excel displays them with a truncated number of decimal or significant digits.


To check if formulas with RANK function work properly try copying one of the 3.5 cells and pasting over all another equal value cells. Repeat this operation for every apparently repeated group of numbers. If formulas with RANK work fine, then go to next step; otherwise upload a sample file following instructions of green sticky post at this forums main page.


Next step: you're done! Try to apply formulas to your actual data and check the results.


Just advise if any issue.


Regards!


EDIT: Now I realize that I didn't correctly understand the issue. Even my answer applies in general, in this case RANK is working as expected.
 
Hi ,


What you have observed is correct ; whenever the RANK function comes across equal values , it allots them an equal rank , and skips that many ranks when it has to allot the next rank.


For instance , in your case , if there are two values which have been allotted a RANK of 2 , there will not be a RANK of 3. The next RANK after 2 will be 4.


Secondly , I copied your data and used the RANK function , and I did not get RANKs of 7 and 8 for the two values of 3.5 ; they were both allotted the RANK of 7. The results I got are as below :

[pre]
Code: 
3.722222222	5
3.277777778	12
3.333333333	11
3.777777778	4
3.5	                7
3.055555556	15
3.388888889	9
3.833333333	2
3.833333333	2
3.944444444	1
3.388888889	9
3.055555556	14
3.111111111	13
3.611111111	6
3.5            	7
The formula I used was : =RANK(number,data range) , where number can be A2 if your data starts from A2 , and data range is $A$2:$A$16. This formula is copied downwards , till B16.


Narayan

P.S. The following is the Excel help on this function :

RANK gives duplicate numbers the same rank. However, the presence of duplicate numbers affects the ranks of subsequent numbers.
For example, in a list of integers sorted in ascending order, if the number 10 appears twice and has a rank of 5,
then 11 would have a rank of 7 (no number would have a rank of 6).

For some purposes one might want to use a definition of rank that takes ties into account. In the previous example,
one would want a revised rank of 5.5 for the number 10. This can be done by adding the following correction factor
to the value returned by RANK. This correction factor is appropriate both for the case where rank is computed
in descending order (order = 0 or omitted) or ascending order (order = nonzero value).

Correction factor for tied ranks=[COUNT(ref) + 1 – RANK(number, ref, 0) – RANK(number, ref, 1)]/2.

In the following example, RANK(A2,A1:A5,1) equals 3. The correction factor is (5 + 1 – 2 – 3)/2 = 0.5
and the revised rank that takes ties into account is 3 + 0.5 = 3.5. If number occurs only once in ref,
the correction factor will be 0, since RANK would not have to be adjusted for a tie.
[/pre]
 
https://www.dropbox.com/s/kgjika5xs4ylb12/Workbook1.xlsx


Hi,


Thanks for your advice but I am still unable to work it. I have attached a link to the sample workbook that I am working on. I am trying to rank the values in column B.


Any help would be appreciated.


Thank you.
 
Hi ,


Can you check out this file ?


http://speedy.sh/fqVBt/Workbook1.xlsx


The technique used is a standard technique , which uses the ROW() function to add / subtract a small value , to generate unique ranks.


Narayan
 
You must log in or register to reply here.
Back
Top