Rank Formula in Excel
- 8 Comment
The Rank Formula that is built into Excel can be a very useful way of speeding up the ranking of several items. Let’s take a look at the formula and see how this can help us out:
=rank(number, ref, order)
where number is the number whose rank you want to find; ref is a reference to the range of numbers you want to rank against (non-numeric values in the range are ignored), and order refers to the way you want to ranking to appear – 0 or blank for descending and a non-zero number for ascending.
EXAMPLES
Example 1: =rank(B2,B2:B7) will return 2. Since we left the order reference blank it ranked in descending order and 97% is the second highest score in the list.
Example 2: =rank(B3,B2:B7,1) will return 1. Since this time we filled in order with a non-zero number it returned in ascending order and 67% was the lowest score.
Example 3: In this example, please refer to the image below of the updated spreadsheet. To obtain this result, all we had to do was take the formula in example 1 and lock some values (=rank(B2, $B$2:$B$7). After you enter this in cell C2, you just need to pull the formula down and it will fill the rest in correctly.
Further Applications
There are many other circumstances where you can use the Rank Formula in Excel. One example that I use it for all the time is to create percentiles (There is actually a percentile function and a percentrank function, which I’ll explore more soon).
** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.
More Excel Tips
8 Comments on this post
Trackbacks
-
Deepak kesarwani said:
It’s exiciting…..i want to learn more….
July 24th, 2009 at 5:41 am -
Eng Fook Kang said:
It’s a great hints and help, epecially the example provide.
August 27th, 2009 at 7:38 am -
Automobile India said:
I want to create a formula in excel that ranks revenue less than or equal to $50,000 a 1, anything from $50,001 – $100,000 a 2 and so on up to a ranking of 5. I think I’m supposed to use the IF function, but I’m not sure. Any ideas?
October 22nd, 2009 at 5:47 am -
Automobile India said:
How do I get the rank function to rank based on how many data points are available? And make this flexible enough so that no matter how many data points I enter it can still work?
November 1st, 2009 at 9:56 pm -
m.kamatchi said:
using rank formula in Students Total and reslut Pass or fail
December 16th, 2009 at 5:51 am -
Ranjeet said:
its an amazing site
February 10th, 2010 at 4:24 am -
Critter said:
I am trying to rank who has had the most opportunities to work overtime, have it available to the rest of the team so they can quickly and easily see who would be the next available person to who judging by who has worked the least amount. Meaning the person with the highest would be ranked number one and if there were not any ties (of which this is not a concern) all the way to 60 because there are presently 60 members on the team. Is this possible?
March 1st, 2010 at 6:50 am
Loading ...
[...] Rank Formula in Excel [...]