RANK Function in Excel


Home >> How to Use Excel Tutorial >> Excel Formulas List >> RANK Function in Excel



In this tutorial, you will learn about the RANK formula in excel. RANK function in excel is useful as a way to find the order position of a number compared to other numbers.


Why We Need to Learn About RANK Formula in Excel?


In a numeral data group that we have in this spreadsheet software, sometimes we want to know what is the order position of one data of the group compared to the other members. The determination of this position can be needed in small to large sequence or the other way around.

For an example of the need to determine the position of a digit, imagine if we need to know the top ten spending posts in the company financial report. This data is needed so we can do the right expense tightening to suppress loss in company business. Or probably when we want to know the most reliable company production machine to be allocated with the production of a top product by looking at the ranking of reliability rate data for all production machines in the company factory on a spreadsheet.

To get data as needed in those two cases, we must know the order of the related numeral data in our spreadsheet software.

For that purpose, we can use RANK function in excel as a way to do the process as needed. RANK formula in excel is important to understand if we need to know the order of our numeral data, especially when a high amount of numerical data that is needed to be compared to that data for the order position determination process.


What is RANK Function in Excel?


RANK function in excel is a formula to do the ranking process for a digit by comparing its value with other digits in this spreadsheet software. RANK formula in excel is one of the formula with a ranking based result besides MAX and MIN. The difference is if MAX and MIN gives the result of numeral data based on the largest or smallest ranking of the data compared to the others, then RANK function in excel gives the result of order position based on the large or small comparison of that numeral data to the other data which is being compared with it.

Here is a brief explanation about the inputs in this formula:


=RANK(number, ref, order)


Note:
  • number = the digit that you want to get the rank of
  • ref = cell range where the group of numbers for the rank comparison is in
  • order = optional. The order sequence for the rank determination, whether it is from small to large or large to small



How to Use RANK Formula in Excel?


The following will explain how to do the ranking process by using RANK function in excel. RANK formula in excel needs the input of number which order position want to be found, cell range consisting of other numbers to be compared of their larger or smaller value to the previous number input, and also the optional input about whether the position will be determined by large to small sequence or the other way around.


Using RANK Function in Excel

  1. Type equal sign ( = ) in the cell where you want to put the result in

    RANK Function in Excel - Screenshot of Step 1

  2. Type RANK (can be with large and small letters) and open bracket sign after =

    RANK Function in Excel - Screenshot of Step 2

  3. Type number or cell coordinate where the number that you want to get the order position of is, after the open bracket sign, then type a comma sign ( , )

    RANK Function in Excel - Screenshot of Step 3

  4. Drag cursor from the most top left to the most bottom right on the cells where the digits which you want to be the comparison for the order position are after the comma sign

    RANK Function in Excel - Screenshot of Step 4

  5. Optional: Type comma sign then type 0 or 1 to tell the formula whether you want the position to be seen based on large to small or small to large sequence. 0 for large to small and 1 to small to large. If there is no input in this part, then it will be considered as 0 or from large to small sequence for the position basis

    RANK Function in Excel - Screenshot of Step 5

  6. Type close bracket sign

    RANK Function in Excel - Screenshot of Step 6

  7. Press Enter
  8. The process of RANK function in excel is done!

    RANK Function in Excel - Screenshot of Step 8



Exercise


After you have learned the steps to do RANK formula in excel from the tutorial, you can practice your understanding so you can become more adept at the skill by doing this exercise!

Download the file from the following link and answer the questions. Please download the answer if you have done the exercise and sure about the result!

Link to download RANK formula in excel exercise file:
Download here

Questions

  1. What is the order position of 387 digit in column B with the sequence basis of large to small?
  2. What is the order position of 717 digit in column C with the sequence basis of small to large?
  3. What is the order position of 484 digit compared to the data from all columns with the sequence basis of small to large?

Link to download answer file:
Download here


Additional Notes


  • If you want, cell coordinates which are near to each other for the RANK formula in excel input can also be written manually separated for the most top-left cell and most bottom-right cell by two dots sign ( : ) (Writing example: B1:D25)
  • There are formulas to find data from the comparison of ranking in this spreadsheet software like MIN and MAX. If you want to learn them too, then you can look at the tutorials from other parts of this Compute Expert blog!




Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!


Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!



Follow our tutorial content also on


CONTACT US PRIVACY POLICY TERMS AND CONDITION AFFILIATE DISCLOSURE @Compute Expert