Jun 15 2007

Rank Formula in Excel

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.


[ad#in-post-ad]

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

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          

26 Comments on this post

Trackbacks

  1. Top Posts in 2008 | Excel Hints wrote:

    [...] Rank Formula in Excel [...]

    January 1st, 2009 at 4:52 pm
  2. EDU 3105 – Teknologi dalam P&P - BORANG MARKAH (MARKSHEET) wrote:
    August 7th, 2011 at 1:14 pm
  3. Top 10% of Sorted Data wrote:

    [...] Or maybe something like this… Rank Formula in Excel [...]

    March 5th, 2013 at 12:03 pm
  1. Deepak kesarwani said:

    It’s exiciting…..i want to learn more….

    July 24th, 2009 at 5:41 am
  2. Eng Fook Kang said:

    It’s a great hints and help, epecially the example provide.

    August 27th, 2009 at 7:38 am
  3. 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
  4. 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
  5. m.kamatchi said:

    using rank formula in Students Total and reslut Pass or fail

    December 16th, 2009 at 5:51 am
  6. Ranjeet said:

    its an amazing site

    February 10th, 2010 at 4:24 am
  7. 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
  8. Mujumdar Madhav said:

    I want to know how to get sum of numbers in Column C if text in Column A is filtered

    March 31st, 2010 at 3:00 am
  9. Karthik said:

    Hai my problem is excel sheet in rank

    general rank formual is ok

    but two columns rank one column is total another coloumn is section

    sectino wise and total wise ranikg procedure plz

    April 19th, 2010 at 5:23 am
  10. sunny said:

    Hai I have excel file with 10 colums. These are track of letters. each letter as an N number like N102 N103 etc.. we have the letter scanned and stored in another folder (data) by the same N number.

    Now I want to create a macro, which when executed should check of the N number from excel sheet and track the same N number in data folder and create a link to the subject.

    Operating system – XP Ms Office 2007.

    May 11th, 2010 at 8:24 am
  11. sudhakar bandari said:

    its an amazing site , but i want base excel formulas

    August 7th, 2010 at 1:41 am
  12. sunil said:

    super , thank you.
    i have problem with date , month and year [ ascending order ].
    when , i tried, that time only dates wise changed but months and year not chnaged.
    i want change date [ ascending order ] , month [ ascending order ] , year [ ascending order ]

    ex : 1 – 01 – 2008 to 15 – 08 – 2010. with names also change.solve this problem.

    August 15th, 2010 at 9:04 am
  13. phunsum said:

    i have a problem with the ranking.

    April 5th, 2011 at 5:44 am
  14. Nelson Bostrom said:

    For some reason it is returning #N/A, rather than the rank when there are identical numbers (such as 2 75%s above). I tried making sure the format of the cells being used in the formula are all numbers, but that doesn’t help.

    April 5th, 2011 at 4:47 pm
  15. john said:

    Can you provide any more detail? I’m not sure why it isn’t working off the top of my head.

    April 7th, 2011 at 10:51 am
  16. vikash said:

    i want to make a mark sheet programme in excel but i don’t know about the function of division (first,second,third).

    April 14th, 2011 at 3:26 am
  17. gopalakrishnarao1 said:

    I have tried ranking, suppose if the numbers are repetitive then How to assign the ranking.

    May 24th, 2011 at 11:50 am
  18. darwin sh damanik said:

    Thank’s for this post helpful, but I have a problem : how to find the average of two or more of the same rank? Example below :
    Score Rank
    45 7
    54 5
    27 8
    55 3
    49 6
    55 3
    66 1
    61 2

    February 28th, 2012 at 3:51 am
  19. Thomas Aloyce said:

    its cool site for excel learners

    September 20th, 2012 at 6:46 am
  20. Fidelis said:

    Can one get discontinuous range from a range with the original relative addresses

    October 8th, 2012 at 9:23 am
  21. thirukganeshan said:

    thanks your great help to find doubt

    December 21st, 2012 at 12:19 pm
  22. Param said:

    Good work

    January 16th, 2013 at 1:27 pm
  23. Sarathy said:

    Thanks and Very good Concept.its very useful for students

    August 7th, 2014 at 8:03 am

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories