RANK.EQ Function

The RANK.EQ function returns the rank of a number in a list that is in no particular order.
This function is very useful when:
1) you have a list of numbers that cannot be sorted into ascending or descending order.
2) you have a list of numbers that contain duplicates.

Analysis ToolPak

There is also Rank functionality included in the Analysis ToolPak add-in.

Duplicates in my list.

In the examples we will consider two types of lists. One without duplicates and one with duplicates.
Notice also that the one without duplicates will be sorted and the one with duplicates will not be sorted.
Whether your list is sorted or not does not matter, what does matter though is whether the list has any duplicates.
If there are no duplicates then every item will have a different rank.
If there are duplicates then the duplicate items will have the same rank.

Ascending Order

Lets consider a small table of data containing the scores obtained by 7 people.
The simplest example of this function is to return the rank of a series of numbers in ascending order
Notice that the third argument can either be left blank or have a "0". Zero is the default if no argument is specified.

Using the function in this was will give duplicate numbers for the same rank.

Descending Order

We have sorted the scores from highest to lowest to help illustrate how this works.
It is also possible to return the rank in descending order. Here the argument has been changed to "1".

Using the function in this was will give duplicate numbers for the same rank.

Displaying the Average

You might find it more useful to display the rank as an average rather than as the lowest rank.
For example because the value 300 appears three times it is now displayed with a rank of ?
This formula must be entered as an array formula using (Ctrl + Shift + Enter).

Removing the Duplicates

It is important to note that the last IF function must extend to one "extra" cell below the actual data.
This formula must be entered as an array formula using (Ctrl + Shift + Enter).

Exactly what happens when you have duplicate values can be confusing.

Important

Your list does not have to be sorted.