# Ranking Data within Excel

#### jimmybangs

##### New Member
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

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### Gerald Higgins

##### Well-known Member
James

How many numbers do you have ?

#### jimmybangs

##### New Member
Its around 10,000

#### Gerald Higgins

##### Well-known Member
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

##### New Member
Yes, this is correct, any thoughts

#### jimmybangs

##### New Member
There could be duplicates in there too...

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

#### Gerald Higgins

##### Well-known Member
If your numbers are in column A, try this
Code:
``=(((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
Code:
``=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.

Replies
1
Views
139
Replies
4
Views
693
Replies
5
Views
877
Replies
7
Views
870
Replies
8
Views
225

1,190,781
Messages
5,982,867
Members
439,802
Latest member
Teiho

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back