5 Jan 2014

How to quickly find rank in a data table

You might have came across various data tables, where you wish to sort data or rank them as per the performance. There is a Function called RANK, which you can use to quickly find the performance ranking.
Let us take an example of total number of candidates that have passed All India Common Entrance test results. In this table, we have total number of students that passed the exam, now we want to know how each state can be categorized into ranks.

We can use the function RANK as shown in above example. The function argument is like this,
= RANK(number, ref[order])

number is the value, that is to be tested against a range for ranking
ref is the data range to be searched
[order] is the sorting order of the rank values , where 0 is descending and 1 is ascending.

Note: RANK function might have compatibility concerns with Excel prior to 2007 and earlier versions. For higher version of Excel, you can use =RANK.AVG( 

Tip: Here we use a shortcut, to fix the data range, by inserting $ before the Row and Column number. eg $B$3; so that we can copy the same formula for the other cells.


comments powered by Disqus