Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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.

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

enter image description here

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


Addendum:

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:

    =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:

enter image description here

share|improve this answer
    
What if does field aren't together? – fox Mar 21 '13 at 13:14
1  
@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.

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

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .