# new column which numerically ranks numbers from lowest to highest

#### Haydo

##### Board Regular
On an excel sheet, I have lists of numbers which appear like this in different columns, and there are different sets of numbers with black rows in between.

48
127.54
137.54
68
98
9.45
890.5
1.89
2.35
6.7

i would like to have a 'ranking order' from lowest to highest, in a new column which places a '1' adjacent to the lowest number, '2' adjacent to the 2nd lowest number, etc
It will look like this.

please note - the equal 8th lowest shared by 2 numbers. If two numbers are equal i would prefer this equal option if possible.

48 5
127.54 8
127.54 8
68 6
98 7
9.45 4
890.5 10
1.89 1
2.35 2
6.7 3

I have separate sets of numbers (separated by blank rows) on the same sheet which i would like to apply this code/function to SEPARATELY. (each set of numbers has its own rank from lowest to highest) I can make adjustments to the function/code to apply to the row/column numbers if required.

Is there a function for this task? Is it best to use code? If so, if someone could assist me with a code, I would greatly appreciate it.

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### mumps

##### Well-known Member
Assuming your numbers are in column A starting in row 1, place this formula in B1 and copy it down column B as far as you need it.
=IFERROR(RANK(A1,\$A\$1:\$A\$20,1),"")

Last edited:

#### Haydo

##### Board Regular
Thank you. I have started working with this. I will need some time to work it out for all the options, but it is producing a good result. Cheers.

#### mumps

##### Well-known Member
You are very welcome.

#### Amanda Wilso

##### Board Regular
= RANK (A2, \$A\$2: \$A\$11)

or

Click a cell in one of the columns That You want to sort.
Data in adjacent columns will be sorted based on the column That You sort.
On the Data tab, under Sort & Filter, click the arrow next to Sort.

Replies
4
Views
249
Replies
4
Views
696
Replies
8
Views
127
Replies
5
Views
878
Replies
11
Views
2K

1,190,896
Messages
5,983,441
Members
439,843
Latest member
PlanetFitness

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