Ranking Data within Excel

[jimmybangs]

Hi,

I'm looking for a solution to rank data within excel but with a bit of a difference in that i would like the rank to only be between 1 and 10 and go to three decimal places...The numbers range from 50 to around 100000.

Has anybody got any ideas?

I've tried percentile ranking and standard deviation but maybe i'm doing it wrong?

Thanks,

James

 

[Gerald Higgins]

James

How many numbers do you have ?

 

[jimmybangs]

Its around 10,000

 

[Gerald Higgins]

OK. So, do you want the first one to be ranked 1, the LAST one to be ranked 10, and everything between some decimal such as 5.678 ?

 

[jimmybangs]

Yes, this is correct, any thoughts

 

[jimmybangs]

There could be duplicates in there too...

I just can't think of how I could do it...

 

[Gerald Higgins]

If your numbers are in column A, try this

=(((RANK(A4,A:A,1)-1)/(COUNT(A:A)-1))*9)+1

This will give you a decimal ranking, such as 1.2345678

If you want to convert this to 1.235
EITHER
format it to show 3 decimal places, but leave the underlying value unchanged
OR
wrap the formula within one of the ROUND functions, like this

=ROUND((((RANK(A2,A:A,1)-1)/(COUNT(A:A)-1))*9)+1,3)

If there are duplicate values in your data, this formula will give them the same ranking.

Also, be aware that you will get duplicate rankings from different values, after you've rounded the formula, if you have more than 10,000 different numbers in your data set.