Functions: RANK

Hi there!! Departing from the regular series on functions (Lookup & Reference) that we are seeing in last few posts, today I am going to introduce RANK function in this post. RANK can be found under 'Statistical' category in Excel built in functions. I thought of writing about this function because I recently used it and found myself researching about tweaking this so that I can adapt it to more practical solutions.

RANK function returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. That means, if you sort the list, the rank of the number would be its position from top. For example, you have a list of marks secured by a set of 7 students and you want to give them ranks from 1 to 7 based on their performance in which 1 being the top and 7 being the lowest marks. You can use RANK in order to get the desired result. Let's further analyse this function syntax and usage.

RANK Function Syntax

RANK(number,ref,[order])

number: Is the number you are looking to find a RANK for.

ref: Is the list of numbers in which you want to find the rank of a number

order: This is an optional argument. If you omit or enter 0 or False, RANK returns the rank from high to low. In our example above, person who secured the top marks will get a rank of 1. Any other value for this argument returns the rank from low to high. Same example above returns rank of 1 to lowest mark.

Example:

Following our example above, I have a list of marks for 7 students and want to find the ranks for each of them. I want to give them ranks from 1 to 7 based on their performance in which 1 being the top and 7 being the lowest marks. See below on how RANK can help us.

That's it. We get the ranks for each item. Other way around, if I want to rank them in reverse order, a formula like below would help.

Points to remember

• If you are applying RANK to multiple numbers by dragging it to subsequent cells, make sure you use absolute reference in ref.
• Of course, number you are looking to give a rank should be available in the list of numbers. Otherwise RANK returns an error (#N/A).
• Make sure that list for second argument contains numbers only. Non-numbers or numbers formatted as text will result into #N/A error. RANK will not give rank to this item and continues ranking other numbers.
• Zero is also a number for RANK function hence it will be given a rank. But because of any reason, what if you want to exclude zero while giving ranks? Actually this is the situation I faced couple of days back. I was analysing returns given by certain mutual funds in FY13. There is a big list of numbers varying from -40% to +120%. A return of 0% means that the mutual fund did not participate in that category or data is not available. Hence it should not be ranked. I end up tweaking RANK function to suit this requirement. Refer to my solution in 'Avoid zeros' section below.
• RANK gives same rank to duplicate numbers but effects the sequence of ranks. For example, if you have a list of numbers 10, 20, 20, 30, 40, 50 and applied RANK function for all the numbers, ranks of second AND third number will be 2 and 2 but the fourth number will have a rank 4. Meaning there is no rank 3 given to any number in the list.

Avoid Zeros

In some practical cases, we need not give ranks to zeros. If your data contains zeros, RANK assigns a rank to them. See the standard RANK performance in below picture. See the cells highlighted in blue. Zeros got the rank 5.

So what if I do not want to rank zeros in the above example because zero means return figure is not available hence cannot be ranked. Let's see how we can use IF and RANK combination to do this.

Same data. But see the highlighted cells. There is no ranking to zeros and other numbers got ranks ignoring zeros. (Notice the difference of 2 in all ranks compared to earlier ranks including zeros). Mission accomplished.. Let's analyse what our formula is doing here.

We used a basic IF formula to check if number we are checking is zero. If true then return a null value (""), if false then further use rank function. For cell B2, Excel evaluated the function like below.

1. Check if A2 = 0
2. If above result is True then show "" as a result. No further analysis.
3. If result is False then result will be result of 

RANK(A2,$A$2:$A$11)-IF(A2<0,COUNTIF($A$2:$A$11,0),0)

So our first requirement, showing null (blank in the cell) for zeros is achieved by above simple steps. Showing correct ranking for cells not containing zeros is the work of 3rd step above. First part of the above function calculates the rank including zeros using standard RANK syntax.

RANK(A2,$A$2:$A$11)

Result for first number using this formula would fetch 10. Careful now. Ignoring zeros in ranking WILL NOT effect positive numbers rankings but the negative number rankings will be reduced by number of zeros in the list. Notice above 2 pictures and find out that positive number rankings have not changed. So the second part of the above function evaluates like shown below.

1. Check if A2 is less than 0 (means negative)
2. If true then count number of zeros in the list using COUNTIF
3. If false then result is 0

Result of second part is 2 in our example (number of zeros are 2, right?). This number will be deducted from result of first evaluation (which is 10) and revised rank 8 is given.

Drag it over other cells and you get ranking ignoring zeros. Though our example shows ignoring zeros, with bit of modification, you can set what ever criteria you wish for. 

Post a comment if you have anything to share regarding this. Happy learning!!