# Excel formula for finding the position of value

Let's say I have values in fields like this: 33, 54 , 61, 13 , 11

What I want to achieve is this (get the positions of values high -> low) and wirte this values in another seperate field

``````33 -> 5
37 -> 2
35 -> 3
34 -> 4
45 -> 1
``````

-

You can use the `RANK` function.

``````=RANK(A1,\$A\$1:\$A\$5,0)
``````

The 3rd parameter determines the order (1 for Ascending, 0 for Descending).

If the values you're trying to rank are in non-contiguous cells (see example above), you can use a named range to refer to them. Here's how, with example:

1. Select the cells / numbers you wish to rank.
2. Click the name box & type a name for them. Press Enter. I chose `scores` for my example.

3. Use the name in the formula to rank each number:

`=RANK(A1,scores,0)`

where `A1` points to a cell/value whose rank you're trying to find out

--
Note that duplicate values will be given "tied" ranks. Example:

-
What if does field aren't together? – fox Mar 21 '13 at 13:14
@fox I'm not sure what you mean. Can you provide a screenshot? If you're dealing with non-contiguous cells, you can create a named range. – Kaze Mar 21 '13 at 13:17
See updated post... – fox Mar 21 '13 at 13:22

You can use the RANK() function to find the rank of a cell in respect to the others. Assuming your column A contains the values, this goes in B1

``````=RANK(A1,\$A\$1:\$A\$5,0)
``````

Copy to each cell in B.

The final "0" denotes descending, ie, your highest value gets rank 1.

-

If you have a small number of non-contiguous cells which contain the values to be ranked, e.g. A11, A13, A33, A54, A61 then you can still use `RANK` function like this

`=RANK(A11,(A\$11,A\$13,A\$33,A\$54,A\$61))`

a named range will also work as per Kaze's suggestion....

-
Doesn't work. Actually I'm doing this in libreCalc. Does it maybe have somekind of different syntax. – fox Mar 21 '13 at 13:38
It works OK in Excel - haven't tried libreCalc..... – barry houdini Mar 21 '13 at 13:57