Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 

enter image description here

share|improve this question
up vote 2 down vote accepted

You can use the RANK function.


enter image description here

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.

    enter image description here

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


    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:

enter image description here

share|improve this answer
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


Copy to each cell in B.

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

share|improve this answer

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


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

share|improve this answer
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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.