EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Functions RANK Excel Function
Secondary Sidebar
Excel Functions
  • Excel Functions
    • Excel Column Total
    • VLOOKUP to Return Multiple Values
    • User Defined Function in Excel
    • Excel SWITCH Function
    • Excel FV Formula
    • Excel AVERAGEIF
    • Simple Formula in Excel
    • Excel Scatter Chart
    • Excel Bar Chart
    • LINEST Excel Function
    • RANK Excel Function
    • Excel MOD Function
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Certification Course
  • EXCEL Certification COURSE

RANK Excel Function

By Jeevan A YJeevan A Y

RANK Function in Excel

Excel RANK Function (Table of Contents)

  • RANK in Excel
  • RANK Formula in Excel
  • How to Use RANK Function in Excel?

RANK in Excel

Rank function in excel is used for finding out the best sequence position of any selected cell from the given hierarchy or range, which is only applicable for number. And it is because Rank can only be measured in numbers. If we have 5 number and want to find the rank (or position) of any number, we simply need to select the range and then choose the order in which rank we want.

RANK Formula in Excel:

Below is the RANK Formula in Excel:

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

RANK Formula

Explanation of RANK Function in Excel

RANK Formula in Excel includes two mandatory arguments and one optional argument.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,639 ratings)
  • Number: This is the value or number we want to find the rank.
  • Ref: This is the list of numbers in a range or in an array you want to your “Number” compared to.
  • [Order]: Whether you want your ranking in Ascending or Descending order. Type 0 for descending and type 1 for ascending order.

Ranking products, people, or services can help you to compare one against another. The best thing is we can see which is at the top, which is at the average level, and which is at the bottom.

We can analyze each one of them based on the rank given. If the product or service is at the bottom level, we can study that particular product or service and find the root cause for that product or service poor performance and take necessary action against them.

Three Different Types of RANK Functions in Excel

If you start typing the RANK function in excel, it will show you 3 types of RANK functions.

  1. RANK.AVG
  2. RANK.EQ
  3. RANK

In Excel 2007 and earlier versions, and only the RANK Function was available. However, later on, a RANK Function has been replaced by a RANK.AVG and RANK.EQ functions.

Though RANK Function still works in recent versions, it may not be available in future versions.

RANK Formulas

How to Use RANK Function in Excel?

RANK Function in Excel is very simple and easy to use. Let understand the working of RANK Function in Excel by some RANK Formula example.

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

Example #1

I have a total of 12 teams that participated in the Kabaddi tournament recently. I have team names, their total points in the first two columns.

I have to rank each team when I compared it to other teams on the list.

Example 1

Since RANK works only for compatibility in earlier versions, I am using RANK.EQ function instead of a RANK Function here.

Note: Both work exactly the same way.

Apply the RANK.EQ function in cell C2.

RANK.EQ formula 1

So the output will be :

RANK Example 1-3

We can drag the formula by using Ctrl + D or double click on the right corner of the cell C2. So the result would be:

Drag

Note: I have not mentioned the order reference. Therefore, excel by default ranks in descending order.

  • =RANK.EQ (B2, $B$2:$B$13) returned a number (rank) of 12. In this list, I have a total of 12 teams. This team scored 12 points, which is the lowest among all the 12 teams we have taken into consideration. Therefore, the formula ranked it as 12, i.e. the last rank.
  • =RANK.EQ (B3, $B$2:$B$13) returned a number (rank) of 1. This team scored 105 points, which is the highest among all the 12 teams we have taken into consideration. Therefore, the formula ranked it as 1, i.e. first rank.

This is how RANK or RANK.EQ function helps us find out each team’s rank when we compared against each other in the same group.

Example #2

One common problem with RANK.EQ function is if there are two same values, then it gives the same ranking to both the values.

Consider the below data for this example. I have a batsmen name and their career average data.

Example 2

Apply the RANK.EQ function in cell C2, and the formula should like the below one.

=RANK.EQ(B2,$B$2:$B$6)

RANK.EQ function 2

So the output will be :

RANK Example 2-3

We can drag the formula by using Ctrl + D or double click on the right corner of the cell C2. So the result would be:

Result of Rank Function 1

If I apply a RANK formula to this data, both Sachin and Dravid get the rank 1.

RANK Example 2-5

My point is if the formula finds two duplicate values, then it has to show 1 for the first-ever value is found and the next value to the other number.

There are many ways we can find the unique ranks in these cases. In this example, I am using RANK.EQ with COUNTIF function.

RANK.EQ Formula with COUNTIF function

So the output will be :

Result of RANK.EQ with COUNTIF function

We can drag the formula by using Ctrl + D or double click on the right corner of the cell D2. So the result would be:

Drag

The formula I have used here is

=RANK.EQ (B2, $B$2:$B$6) this will find the rank for this set.

COUNTIF ($B$2:B2, B2) – 1. COUNTIF formula will do the magic here. For the first cell, I have mentioned $B$2:B2 means at this range, what is the total count of the B2 value then deduct that value from 1.

The first RANK returns 1 and COUNTIF returns 1, but since we mentioned -1, it becomes zero; therefore, 1+0 = 1. For Sachin, RANK remains 1.

For Dravid, we got the Rank as 2. Here RANK returns 1 but COUNTIF returns 2 but since we mentioned -1 it becomes 1 therefore 1 + 1 = 2. The rank for Dravid is 2, not 1.

This is how we can get unique ranks in case of duplicate values.

Unique values

Things to Remember

  • A RANK Function is replaced by RANK.EQ in 2010 and later versions.
  • A RANK Function in Excel can accept only numerical values. Anything other than numerical values, we will get an error as #VALUE!
  • If the number you are testing is not present in the list of numbers, we will get #N/A! Error.
  • The RANK function in Excel gives the same ranking in the case of duplicate values. Anyhow, we can get unique ranks by using the COUNTIF function.
  • Data need not sort in ascending or descending order to get the results.

Recommended Articles

This has been a guide to RANK in Excel. Here we discuss the RANK Formula in Excel and How to use a RANK Function in Exel, along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. Excel Formula For Rank
  2. Percentile Rank Formula
  3. Excel Evaluate Formula
  4. SUMPRODUCT Formula in Excel
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Training (23 Courses, 9+ Projects)4.9
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download RANK Function Excel Template

EDUCBA

Download RANK Function Excel Template

EDUCBA

डाउनलोड RANK Function Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more