Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

3 Answers

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. –  Ellesa Mar 21 '13 at 13:17
    
See updated post... –  fox Mar 21 '13 at 13:22
add comment

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
add comment

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
add comment

Your Answer

 
discard

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.