Unique Rank

[GB]

Hi,
I am trying to rank some data in column A from highest value to lowest value. Each rank value must be unique. I have sorted the data by col A descending. Some of the values in column A are the same, but most are different.

in all cases the unique rank formula works perfectly, except for the value in cell B237. I have used the following formula...

=RANK(A2,$A$2:$A$426,0)+COUNTIF(A$2:A2,A2)-1

but can't work out why this rank value is incorrect. It should be 236.

Any help in solving this simple problem would be appreciated.
Regards
GB

 

[NARAYANK991]

Hi ,

The reason for the problem is that the value 4.68493157894737 occurs twice , once in row 236 , and once in row 237.

The data in row 236 is having a rank of 235 ; adding a COUNTIF result of 1 and subtracting 1 gives a final rank of 235.

The data in row 237 gets a rank of 236 from the RANK function ; adding a COUNTIF result of 2 (since it is the second occurrence of this value) and subtracting 1 gives a final rank of 237.

Why two values which are identical should have different ranks is a question I am not able to answer.

See the attached file for a workaround.

Narayan

 

[bosco_yip]

Try to activate A236 and A237 by :

1] Put the mouse cursor at the end of cell A236 formula bar, then hit "Enter".

2] And do the same process to A237, A236 will show 235 and A237 will show 236

Regards

 

[GB]

Hi Narayan,
thanks for your reply, but you will notice for example there are quite a few duplicates in the data, (e.g. A231 & A232 are the same value) yet the rank calculation worked in all cases but the one I highlighted as the problem.

Hi Bosco,
that is useful to know, but definitely not obvious given the number is a number and not text. That has resolved the issue, thank you.

Regards
GB

 

[bosco_yip]

This happened maybe your data copied from outside source.

You can activate whole column A by :

Select whole column A >> "Data" >> "Text to Columns" >> Choose "Delimited", then click "Finish".

Regards
Bosco