CODEWITHSUNDEEP
excelsortingexcel-formula
sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15591

Get order of a number in a list contained in a range, unsorted

I have a range containing numbers, e.g.,

+---+-----+
|   |  A  |
+---+-----+
| 1 |  21 |
| 2 |  34 |
| 3 |   0 |
| 4 |  82 |
| 5 | 112 |
| 6 |  92 |
| 7 | 102 |
| 8 |  52 |
| 9 |  67 |
+---+-----+

I want a formula to find the order of each of these numbers in the sorted list. E.g, for A3 (=0), it would return 1, and so on.

So far, I could do that with two columns (instead of one):

In B1, use =SMALL(A$1:A$9,ROW()-ROW(A$1)+1) (adapted from here, but useful for a list in a row not starting at 1 with suitable referencing). In C1 use =MATCH(A1,B$1:B$9,0). Then copy-paste downwards, to obtain the table below.

I am looking for the same, but sparing the intermediate column.

+---+-----+-----+---+
|   |  A  |  B  | C |
+---+-----+-----+---+
| 1 |  21 |   0 | 2 |
| 2 |  34 |  21 | 3 |
| 3 |   0 |  34 | 1 |
| 4 |  82 |  52 | 6 |
| 5 | 112 |  67 | 9 |
| 6 |  92 |  82 | 7 |
| 7 | 102 |  92 | 8 |
| 8 |  52 | 102 | 4 |
| 9 |  67 | 112 | 5 |
+---+-----+-----+---+

Upvotes: 1

Views: 2338

Answers (2)

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15591

RANK is the (original) function to use.

In addition, from Excel 2010 help for RANK: "IMPORTANT This function has been replaced with one or more new functions that may provide improved accuracy and whose names better reflect their usage. This function is still available for compatibility with earlier versions of Excel. However, if backward compatibility is not required, you should consider using the new functions from now on, because they more accurately describe their functionality. For more information about the new functions, see RANK.AVG function and RANK.EQ function. "

Upvotes: 1

Martin Jackson
Martin Jackson

Reputation: 450

Try this in B1 and pull down the formula, the 1 at the end signifies ascending order:

=RANK(A1,A:A,1)

Upvotes: 6

Related Questions