MrExcel Publishing
Your One Stop for Excel Tips & Solutions

HELP!!! Ranking with negative and positive numbers

Posted by Robin on March 06, 2001 7:13 PM

I am trying to rank numbers with negative and postive numbers. Example



Zero being the best ranking. Less than zero needs to be ranked 1-44 and greater than zero needs to be ranked 1-44. Also, when you rank a list of numbers and say you have 2 numbers ranked the same and it skips a number, how can I rank without it skipping numbers. Example 1,1,3 Can I fix it where it will show me 2.

Posted by GregA on March 07, 2001 4:19 AM


Assume your data is in column A (A1:A15). Then create two new columns, B and C. In column B the formula should take the absolute value. B1 would be:

Populate column B, and then sort columns A and B on the values in B.

Once your data is sorted, you can use the Edit>Fill>Series function to create a sequential numbering of the data in column C.

Hope that helps.