RANK Function in Excel

Excel RANK Function

Excel Rank formula is used to gives us the rank of a given data set of numbers based on the data set or we can say by comparing the other numbers in the data set, the rank function was an inbuilt function for excel 2007 and earlier versions, for newer versions above 2007 we have an inbuilt function as Rank.Avg and Rank.Eq functions.

Rank Function is a built-in function in MS Excel. It falls under the category of STATISTICAL functions in Excel. The function is used to get the rank of a given number from the list of numbers.


RANK Formula

The rank formula in excel has three arguments, out of which the first two are required, and the last one is optional.

  1. number A number for which the rank is to be found out.
  2. ref = the range of numbers w.r.t which the rank of a given number is to be found
  3. order = the order (ascending or descending) in which the numbers should be ranked. 0 = Descending order; 1 = Ascending order. If the order is omitted, the default value is zero, i.e., descending order.

How to Use RANK Function in Excel? (with Examples)

The given function is a Worksheet (WS) function. As a Worksheet function, it can be entered as a part of the formula in a worksheet cell. Refer to the examples explained in this article as you proceed to learn more.

You can download this RANK Function Excel Template here – RANK Function Excel Template

Example #1 – Find Steve’s Rank in Maths

RANK Function Example 1

  • In the above example, the Marks of the Students in Maths and Science subjects are shown. To find Steve’s rank in Maths, we would use the Excel RANK function.
  • The first parameter is the input value, of which the rank is to be calculated. Here, B6 represents Steve’s marks in Maths, i.e., 68.
  • The 2nd parameter, the total cells containing Maths marks are from B1 to B11. So, the range is B1:B11, which comprises all students’ marks in Maths.

RANK Function Example 1-1

  • The highest value represents the rank 1, and the lowest representing the last rank. So, the 3rd parameter is skipped, and Excel treats it as 0; the default order is descending.
  • RANK (B6, B1: B11) returns the rank of the student named Steve in subject Maths as 8, 50 being the lowest (B2) and 99 (B10) being the highest marks in Maths subject.

RANK Function Example 1-2

Example #2 – Find the Lowest Runtime from the Race Record

RANK Function Example 2

  • In the above example, different Time values are given. We have to find the lowest runtime from the given records.
  • The time values range from cell B1: B6. So, the cell range is named as a table and given the name ‘my time.’ It acts as an alias for B1: B6.

RANK Function Example 2-1

  • Unlike example #1, here, the lowest runtime indicates the 1st rank, and the highest runtime indicates the last rank. So, we need to apply ascending order while calculating the rank in excel. Hence, the 3rd parameter is given as 1.
  • So, RANK (B3, my time, 1) returns the lowest runtime as the rank 1 from the given race times.

RANK Function Example 2-2

  • As a result, at 4:30 (cell B3), the lowest one is recorded as rank 1, and at 9:00 (cell B5), the longest one is recorded as the last, i.e., 4th rank.

Example #3 – Value Not Present

RANK Formula in Excel Example 3

  1. As shown in the above figure, the 1st parameter, i.e., the value for which the rank is to be calculated, is NOT present in the range of cells given in the 2nd parameter, then the excel rank formula returns #NA! indicating that the value is invalid, i.e., not present in the given range of reference cells.
  2. As shown in the above figure, number 59 is not present in the range, and hence #NA! is returned as a result shown in cell C8.
  3. One must correct the 1st parameter in such a case.

Example #4 – Text Formatted Numerical Values

RANK Formula in Excel Example 4

  • As shown in the above figure, the 1st parameter, i.e., the value for which the rank is to be calculated, is given in the text format, i.e., “5,” as shown in the above figure. The value does not remain a number as it is quoted in double-quotes and hence treated as a text.
  • Since the rank cannot be calculated over text data, the function returns #N/A! an error indicating that the value is Not Available in the given range of cells. The resultant cell C6 has #N/A returned as the result of the function.

Text Formatted Numerical Values Example 4-1

  • One must correct the 1st parameter in such a case.

Things to Remember

  1. The minimal parameter the Excel RANK function takes is 2.
  2. The 3rd parameter of the RANK function is optional and zero (descending order) by default. If specified as 1, the function treats it as ascending order.
  3. There is no RANKIF formula to perform a conditional ranking similar to COUNTIF in Excel. It needs the usage of an additional Excel function to achieve the same.
  4. When the given number for which the rank is to be calculated (1st parameter) is not present in the range of cells given as a reference to the function (2nd parameter), then #N/A! error occurs.
  5. The rank formula does not support any text values or the text representation of the numeric values as the RANK cannot be applied to text data. If provided so, this excel function returns #N/A error, as explained.

Recommended Articles

This has been a guide to RANK Function in Excel. Here we discuss how to use the RANK function in Excel along with practical examples and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *