RANK.EQ
Returns the rank of a number in a list of numbers.
=RANK.EQ(number, ref, [order])This static page keeps the function indexed for search, while the spreadsheet app handles interactive exploration and saved formulas.
What the function does
Overview
Returns the rank of a number in a list of numbers.
The RANK.EQ function determines the statistical rank of a specific numeric value relative to a list of other values. If multiple values in the range are identical, they receive the same rank (the highest possible rank for that group), creating a 'gap' in the sequence (e.g., if two items tie for 2nd, the next item is ranked 4th). For best results, always use absolute cell references (e.g., 2:10) for the 'ref' argument to prevent the range from shifting when copying the formula. Note that the function ignores non-numeric values and empty cells within the reference range. If you require a unique rank for every item regardless of ties, you must supplement this function with a COUNTIF adjustment to break ties based on row position.
Quick reference
Syntax
=RANK.EQ(number, ref, [order])Inputs
Arguments
Example: B2
Example: $B$2:$B$100
Example: 0
Formula patterns
Examples
Standard descending ranking for sales performance
=RANK.EQ(B2, $B$2:$B$20, 0)Ascending rank for competition finish times
=RANK.EQ(C2, $C$2:$C$15, 1)Rank with unique tie-breaking logic
=RANK.EQ(B2, $B$2:$B$10, 0) + COUNTIF($B$2:B2, B2) - 1Avoid these issues
Common Errors
#N/A
Cause: The number being ranked does not exist within the specified reference range.
Fix: Ensure the first argument (number) is actually present in the second argument (ref) range.
#VALUE!
Cause: The number argument or values within the reference range are text strings rather than numbers.
Fix: Verify that all data is formatted as numbers and remove any non-numeric characters or hidden spaces.
Platform support
Compatibility
Source: Microsoft Support
Common questions
Frequently Asked Questions
Returns the rank of a number in a list of numbers.
number: The value to rank ref: The list of numbers to compare against order: 0=Descending (default), 1=Ascending
#N/A: Ensure the first argument (number) is actually present in the second argument (ref) range. #VALUE!: Verify that all data is formatted as numbers and remove any non-numeric characters or hidden spaces.