Part 1: Introduction to the RANK Function in Excel
Definition
The RANK function in Excel is a statistical function that returns the rank of a number within a list of numbers. The rank of a number is its size relative to other values in an index. If you were to sort the list, the rank of the number would be its position.
Purpose
The RANK function is used when you want to find the rank of a specific number in a list. This can be useful in various business scenarios, such as ranking sales data, employee performance scores, or customer satisfaction ratings.
Syntax & Arguments
The syntax for the RANK function is as follows:
RANK(number, ref, [order]) The RANK function has the following arguments:
- number – This is required. The number whose rank you want to find.
- ref – This is required. An array of, or a reference to, a list of numbers. Non-numeric values in ref are ignored.
- order – This is optional. A number specifies how to rank a number. If the order is 0 (zero) or omitted, Excel ranks the number as if the ref were a list sorted in descending order. If the order is any nonzero value, Excel ranks the number as if the ref were a list sorted in ascending order.
Return Value
The RANK function returns the rank of a number in a list of numbers.
Remarks
RANK gives duplicate numbers the same rank. However, the presence of duplicate numbers affects the ranks of subsequent numbers. For example, in a list of integers sorted in ascending order, if the number 10 appears twice and has a rank of 5, then 11 would have a rank of 7 (no number would have a rank of 6).
For some purposes, one might want to use a definition of rank that considers ties. In the previous example, one would wish to have a revised rank of 5.5 for 10. This can be done by adding the following correction factor to the value returned by RANK. This correction factor is appropriate for where rank is computed in descending order (order = 0 or omitted) or ascending order (order = nonzero value).
Correction factor for tied ranks=[COUNT(ref) + 1 – RANK(number, ref, 0) – RANK(number, ref, 1)]/2.
Part 2: Examples of Using the RANK Function in Excel
Example 1: Ranking Sales Data
Purpose: To rank the sales data of different products.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product ID | Sales | Rank Formula | Rank |
| 2 | 1 | 500 | =RANK(B2, $B$2:$B$4, 0) | 3 |
| 3 | 2 | 600 | =RANK(B3, $B$2:$B$4, 0) | 2 |
| 4 | 3 | 700 | =RANK(B4, $B$2:$B$4, 0) | 1 |
Explanation: In this example, we use the RANK function to rank the sales data of different products. The formula =RANK(B2, $B$2:$B$4, 0) is entered in cell C2, =RANK(B3, $B$2:$B$4, 0) is entered in cell C3, and =RANK(B4, $B$2:$B$4, 0) is entered in cell C4. The results in column D would be the ranks of the sales data for each product, with 1 being the highest sales.
Example 2: Ranking Employee Performance Scores
Purpose: To rank the performance scores of employees.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee ID | Performance Score | Rank Formula | Rank |
| 2 | 1 | 85 | =RANK(B2, $B$2:$B$4, 0) | 3 |
| 3 | 2 | 90 | =RANK(B3, $B$2:$B$4, 0) | 2 |
| 4 | 3 | 95 | =RANK(B4, $B$2:$B$4, 0) | 1 |
Explanation: In this example, we use the RANK function to rank the performance scores of employees. The formula =RANK(B2, $B$2:$B$4, 0) is entered in cell C2, =RANK(B3, $B$2:$B$4, 0) is entered in cell C3, and =RANK(B4, $B$2:$B$4, 0) is entered in cell C4. The results in column D would be the ranks of the performance scores for each employee, with 1 being the highest score.
Example 3: Ranking Customer Satisfaction Ratings
Purpose: To rank the satisfaction ratings of customers.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer ID | Satisfaction Rating | Rank Formula | Rank |
| 2 | 1 | 4.5 | =RANK(B2, $B$2:$B$4, 0) | 2 |
| 3 | 2 | 4.7 | =RANK(B3, $B$2:$B$4, 0) | 1 |
| 4 | 3 | 4.3 | =RANK(B4, $B$2:$B$4, 0) | 3 |
Explanation: Here, we use the RANK function to rank customers’ satisfaction ratings. The formula =RANK(B2, $B$2:$B$4, 0) is entered in cell C2, =RANK(B3, $B$2:$B$4, 0) is entered in cell C3, and =RANK(B4, $B$2:$B$4, 0) is entered in cell C4. The results in column D would be the ranks of the satisfaction ratings for each customer, with 1 being the highest rating.
Example 4: Ranking Website Load Times
Purpose: To rank the load times of different websites.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Website ID | Load Time (s) | Rank Formula | Rank |
| 2 | 1 | 2.5 | =RANK(B2, $B$2:$B$4, 1) | 1 |
| 3 | 2 | 3.0 | =RANK(B3, $B$2:$B$4, 1) | 2 |
| 4 | 3 | 2.8 | =RANK(B4, $B$2:$B$4, 1) | 3 |
Explanation: In this example, we use the RANK function to rank the load times of different websites. The formula =RANK(B2, $B$2:$B$4, 1) is entered in cell C2, =RANK(B3, $B$2:$B$4, 1) is entered in cell C3, and =RANK(B4, $B$2:$B$4, 1) is entered in cell C4. The results in column D would be the ranks of the load times for each website, with 1 being the fastest load time.
Example 5: Ranking Product Ratings
Purpose: To rank the ratings of different products.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product ID | Rating | Rank Formula | Rank |
| 2 | 1 | 4.5 | =RANK(B2, $B$2:$B$4, 0) | 2 |
| 3 | 2 | 4.7 | =RANK(B3, $B$2:$B$4, 0) | 1 |
| 4 | 3 | 4.3 | =RANK(B4, $B$2:$B$4, 0) | 3 |
Explanation: In this example, we use the RANK function to rank the ratings of different products. The formula =RANK(B2, $B$2:$B$4, 0) is entered in cell C2, =RANK(B3, $B$2:$B$4, 0) is entered in cell C3, and =RANK(B4, $B$2:$B$4, 0) is entered in cell C4. The results in column D would be the ranks of the ratings for each product, with 1 being the highest rating.
Example 6: RANK with IF
Purpose: To rank the sales data of different products, but only for those with sales greater than 500.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product ID | Sales | Rank Formula | Rank |
| 2 | 1 | 500 | =IF(B2>500, RANK(B2, $B$2:$B$4, 0), "") | 3 |
| 3 | 2 | 600 | =IF(B3>500, RANK(B3, $B$2:$B$4, 0), "") | 2 |
| 4 | 3 | 400 | =IF(B4>500, RANK(B4, $B$2:$B$4, 0), "") | “” |
Explanation: In this example, we use the IF function within the RANK function to rank only those products with sales greater than 500. The formula =IF(B2>500, RANK(B2, $B$2:$B$4, 0), "") is entered in cell C2, =IF(B3>500, RANK(B3, $B$2:$B$4, 0), "") is entered in cell C3, and =IF(B4>500, RANK(B4, $B$2:$B$4, 0), "") is entered in cell C4. The results in column D would be the ranks of the sales data for each product with sales greater than 500, with 1 being the highest sales.
Example 7: RANK with SUM
Purpose: To find the sum of the ranks of two different products.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product ID | Sales | Rank Formula | Rank |
| 2 | 1 | 500 | =RANK(B2, $B$2:$B$4, 0) | 3 |
| 3 | 2 | 600 | =RANK(B3, $B$2:$B$4, 0) | 2 |
| 4 | 3 | 700 | =RANK(B4, $B$2:$B$4, 0) | 1 |
| 5 | =SUM(C2:C4) | 6 |
Explanation: In this example, we use the SUM function with the RANK function to find the sum of the ranks of the sales data for all products. The formula =SUM(C2:C4) is entered in cell C5. The result in cell D5 would be the sum of the ranks of the sales data for all products.
Example 8: RANK with VLOOKUP
Purpose: To find the rank of a specific product’s sales data and then look up the corresponding product ID.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product ID | Sales | Rank Formula | Rank |
| 2 | 1 | 500 | =RANK(B2, $B$2:$B$4, 0) | 3 |
| 3 | 2 | 600 | =RANK(B3, $B$2:$B$4, 0) | 2 |
| 4 | 3 | 700 | =RANK(B4, $B$2:$B$4, 0) | 1 |
| 5 | =VLOOKUP(1, C2:D4, 1, FALSE) | 3 |
Explanation: In this example, we use the VLOOKUP function with the RANK function to find the product ID corresponding to a specific rank. The formula =VLOOKUP(1, C2:D4, 1, FALSE) is entered in cell C5. The result in cell D5 would be the product ID corresponding to the specified rank.
Example 9: RANK with AVERAGE
Purpose: To find the average rank of all products.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product ID | Sales | Rank Formula | Rank |
| 2 | 1 | 500 | =RANK(B2, $B$2:$B$4, 0) | 3 |
| 3 | 2 | 600 | =RANK(B3, $B$2:$B$4, 0) | 2 |
| 4 | 3 | 700 | =RANK(B4, $B$2:$B$4, 0) | 1 |
| 5 | =AVERAGE(C2:C4) | 2 |
Explanation: In this example, we use the AVERAGE function with the RANK function to find the average rank of all products. The formula =AVERAGE(C2:C4) is entered in cell C5. The result in cell D5 would be the average rank of all products.
Example 10: RANK with MAX
Purpose: To find the maximum rank of all products.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product ID | Sales | Rank Formula | Rank |
| 2 | 1 | 500 | =RANK(B2, $B$2:$B$4, 0) | 3 |
| 3 | 2 | 600 | =RANK(B3, $B$2:$B$4, 0) | 2 |
| 4 | 3 | 700 | =RANK(B4, $B$2:$B$4, 0) | 1 |
| 5 | =MAX(C2:C4) | 3 |
Explanation: In this example, we use the MAX function with the RANK function to find the maximum rank of all products. The formula =MAX(C2:C4) is entered in cell C5. The result in cell D5 would be the maximum rank of all products.
Example 11: RANK with MIN
Purpose: To find the minimum rank of all products.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product ID | Sales | Rank Formula | Rank |
| 2 | 1 | 500 | =RANK(B2, $B$2:$B$4, 0) | 3 |
| 3 | 2 | 600 | =RANK(B3, $B$2:$B$4, 0) | 2 |
| 4 | 3 | 700 | =RANK(B4, $B$2:$B$4, 0) | 1 |
| 5 | =MIN(C2:C4) | 1 |
Explanation: In this example, we use the MIN function with the RANK function to find the minimum rank of all products. The formula =MIN(C2:C4) is entered in cell C5. The result in cell D5 would be the minimum rank of all products.
Example 12: RANK with COUNT
Purpose: To count the number of products with a rank greater than 1.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product ID | Sales | Rank Formula | Rank |
| 2 | 1 | 500 | =RANK(B2, $B$2:$B$4, 0) | 3 |
| 3 | 2 | 600 | =RANK(B3, $B$2:$B$4, 0) | 2 |
| 4 | 3 | 700 | =RANK(B4, $B$2:$B$4, 0) | 1 |
| 5 | =COUNTIF(C2:C4, ">1") | 2 |
Explanation: In this example, we use the COUNTIF function with the RANK function to count the number of products with a rank greater than 1. The formula =COUNTIF(C2:C4, ">1") is entered in cell C5. The result in cell D5 would be the number of products with a rank greater than 1.
Example 13: RANK with ROUND
Purpose: To round the ranks of all products to the nearest whole number.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product ID | Sales | Rank Formula | Rank |
| 2 | 1 | 500 | =ROUND(RANK(B2, $B$2:$B$4, 0), 0) | 3 |
| 3 | 2 | 600 | =ROUND(RANK(B3, $B$2:$B$4, 0), 0) | 2 |
| 4 | 3 | 700 | =ROUND(RANK(B4, $B$2:$B$4, 0), 0) | 1 |
Explanation: In this example, we use the ROUND function with the RANK function to round the ranks of all products to the nearest whole number. The formula =ROUND(RANK(B2, $B$2:$B$4, 0), 0) is entered in cell C2, =ROUND(RANK(B3, $B$2:$B$4, 0), 0) is entered in cell C3, and =ROUND(RANK(B4, $B$2:$B$4, 0), 0) is documented in cell C4. The results in column D would be the ranks of all products rounded to the nearest whole number.
Part 3: Tips and Tricks
Handling Ties: The RANK function gives duplicate numbers the same rank. However, the presence of exact numbers affects the ranks of subsequent numbers. If you want to give each number a unique rank, you can use the RANK.EQ function.
Sorting Order: By default, the RANK function ranks numbers as if the reference array were sorted in descending order. If you want to rank numbers as if the reference array were sorted in ascending order, you can set the order argument to any nonzero value.
Non-Numeric Values: The RANK function ignores non-numeric values in the reference array. If your data contains non-numeric values and you want to include them in the ranking, you must first convert them to numeric values.
Updating the Reference Array: When you add or remove numbers from the reference array, you must update the reference in the RANK function to include the new numbers or exclude the drawn numbers.
Using RANK with Other Functions: The RANK function can perform more complex tasks with other Excel functions. For example, you can use the IF function with the RANK function to rank only the numbers that meet a specific condition.