Excel RANK function

Summary

The Excel RANK function returns the rank of a number within a list of numbers. Its primary use is to determine the position of a specific value compared to other values in a dataset. RANK can sort numbers in ascending or descending order, making it versatile for various ranking needs, from performance analysis to sales comparisons.
Syntax
				
					=RANK(number, ref, [order])
				
			
  • number: The number whose rank you want to find.
  • ref: The array or range of numbers to rank against.
  • [order]: [Optional] A number specifying how to rank numbers. If 0 (zero) or omitted, numbers are ranked in descending order, with the largest number being ranked first. If 1, numbers are ranked in ascending order, with the smallest number being ranked first.
Return value
The rank of the number within the list of numbers.

How to use

To determine a value’s rank, input the value and the range of data against which it should be ranked. Optionally, specify the order of ranking. RANK is helpful for quickly identifying a value’s relative position in a dataset, such as a salesperson’s performance among peers or a product’s sales rank.

Examples

Simple RANK
Determining a Salesperson’s Performance Rank: To rank a salesperson’s performance among their team.
				
					=RANK(B2, B2:B10)
				
			
If B2 contains a salesperson’s sales figure and B2:B10 contains the team’s sales figures, this formula returns the salesperson’s rank within the team, with the highest sales figure being ranked first by default.
RANK in Ascending Order
Ranking Test Scores from Lowest to Highest: To find the rank of a test score in ascending order.
				
					=RANK(C2, C2:C10, 1)
				
			
Assuming C2 contains a student’s score and C2:C10 contains all students’ scores, this formula ranks the student’s score from lowest to highest, with the lowest score being ranked first.
RANK with Descending Order
Identifying Product Sales Rank: To rank a product’s sales among various products in descending order.
				
					=RANK(D2, D2:D10, 0)
				
			
If D2 holds the sales figure for a product and D2:D10 contains sales figures for various products, this formula assigns a rank to D2’s sales figure, with the highest figure ranked as number one.

Additional Notes

  • The RANK function has been replaced with RANK.EQ and RANK.AVG in newer versions of Excel to clarify behavior when there are duplicate values in the dataset. RANK.EQ works like RANK, giving duplicate values the same rank, while RANK.AVG will assign the average rank to duplicates.
  • When using RANK, be aware that duplicate values receive the same rank, but this affects the ranking of subsequent numbers. For instance, if two values are ranked as number 1, the next value will be ranked as number 3.

Related Functions

Excel MATCH function

The Excel MATCH function finds the position of a value within a range, essential for dynamic lookups and complex searches.

Excel COUNTIFS function

The Excel COUNTIFS function counts cells meeting multiple criteria, essential for complex data categorization.

Excel SUMIFS function

The Excel SUMIFS function sums numbers based on multiple criteria, enhancing detailed and conditional data aggregation.

Excel SUMIF function

The Excel SUMIF function sums numbers based on a single condition, perfect for conditional totals in data analysis.

Excel COUNTIF function

The Excel COUNTIF function counts cells that meet a single condition, vital for targeted data quantification.

Content Navigation