Apply Excel RANK Function to bring clarity and insight to your data analysis efforts. Whether you’re evaluating employee performance, analyzing sales rankings, or assessing academic results, this powerful tool helps illuminate the standings within your datasets. By mastering the RANK function, you can make informed decisions, recognize top performers, and identify areas for improvement. Integrate this function into your Excel toolkit to enhance your analytical capabilities and transform raw data into actionable information.
This Content Covers:
- What is RANK Function in Excel and why is it used?
- RANK Function Syntax and Arguments.
- How many types of RANK Functions are available in Excel?
- How to apply RANK Function in Excel (Without Order)?
- How to apply RANK Function in Excel (With Order)?
- How to break ties with RANK Function?
1. What is RANK Function in Excel and why is it used?
The RANK function in Microsoft Excel is a function that returns the rank of a number in a list of numbers.
Excel’s Rank function, which is only applicable to numbers, is used to figure out the best sequence position for each selected cell within the parameters of a given range. For example, if we have ten numbers and wish to identify the rank or position of any one of them, we just need to specify the range and the rank we desire.
2. RANK Function Syntax and Arguments.
Syntax: =RANK(number, ref, [order])
Arguments:
- number- This is the value or number whose rank we’re looking for.
- ref- The list of numbers in a range that you want to compare your “Number” against.
- Order(optional)- Whether to rank in ascending or descending order. Type 0 for descending and type 1 for ascending order.
3. How many types of RANK Functions are available in Excel?
Three different RANK functions are shown in Excel when you start typing the RANK function.
- AVG
- EQ
- RANK
4. How to apply RANK Function in Excel (Without Order)?
Suppose we have a data table similar to this in our worksheet and now we need to use the RANK function to generate the Rank for these student’s scores.
Step 1: Insert the following formula in cell C2 and click Enter. The formula has returned 6 which means this student named Piash who scored 64 is at the 6th position among all of these students. Now click on the lower right corner of cell C2 and drag it to C10 to apply the formula in the entire column of this table.
=RANK(B2,$B$2:$B$10)
Step 2: Once you apply the formula in the entire column by dragging C2 down, this will give you the complete rank of these students’ scores. We haven’t used the order argument here but the rank is created in descending order because this is the default setting for RANK function if no order is implemented.
5. How to apply RANK Function in Excel (With Order)?
Syntax for descending order: RANK(number, ref, 0)
Syntax for ascending order: RANK(number, ref, 1)
Now we will see how we can apply the RANK function with an order argument. The rank is set in descending order if the order setting is 0 or if the third parameter is absent. So, when in our last method where we used the RANK function without order argument it returned us with a descending order by default. To use the ascending order, we just have to use 1 inside the formula.
Step 1: Insert the following formula in C2 and click Enter. Drag C2 down to C10.
=RANK(B2,$B$2:$B$10,1)
Step 2: After applying the formula now we can see the highest score which is 91 is ranked 9th among the 9 scores and the lowest score 38 is ranked 1st because we have set the order of this rank as an ascending order.
6. How to break ties with RANK Function?
The picture below focus on cell C5 and C6. Both the cells have returned 4 after using RANK function in this column because both Damien and Faiaz scored 73 in the test. So, both of them are in tie situations regarding their position or rank. To break this tie, we will use the RANK.EQ function with the COUNTIF function as shown below.
Step 1: Apply the formula given below in C2 and drag it downwards to apply it in the whole column.
=RANK.EQ(B2,$B$2:$B$10)+COUNTIF($B$2:B2,B2)-1
Step 2: Now you can see there is no tie between Damien and Faiaz anymore as Damien is ranked 4th and Faiaz 5th.
What this formula does is, =RANK.EQ(B2,$B$2:$B$10) this will find the rank for this set. COUNTIF($B$2:B2,B2)-1 does the main trick here.
For Damien the RANK function returns 4 and the COUNTIF function returns 1. But since we mentioned -1 the calculation becomes: 4+1-1=4. For Faiaz the RANK function returns 4 as well but the COUNTIF function returns 2. So, the calculation stands as: 4+2-1= 5 which is now the current rank of Faiaz.
Application of Apply Excel RANK Function
- Employee Performance Evaluation: Use the RANK function to assess employee performance by ranking them based on sales figures, productivity metrics, or other performance indicators.
- Academic Grading: Apply the RANK function to rank students based on their scores or grades, helping educators identify top performers and students who may need additional support.
- Sales Data Analysis: Utilize the RANK function to order sales data, enabling businesses to identify best-selling products and prioritize inventory and marketing strategies accordingly.
- Financial Data Benchmarking: Employ the RANK function to compare financial metrics, such as profit margins or revenue growth, among different departments, branches, or competitors.
- Sporting Event Results: Use the RANK function to determine the standings or rankings in sports competitions based on times, points, or other measurable outcomes.
- Customer Priority Setting: Apply the RANK function to customer data, such as purchase history or service usage, to prioritize customer service and target marketing efforts.
For ready-to-use Dashboard Templates:
