Description
Use this function to determine the rank of a number within a list of numbers. Supported in Chains. Can be used with CHILDREFS.
Syntax
RANK.EQ(number, ref_1, […, ref_254, [order])
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
number |
Yes | The number whose rank you want to find. | A number, a reference to a cell containing a number, or a formula which results in either of these. |
ref_1, …, ref_n |
Yes | An array of —or a reference to— a list of numbers. | A cell range, a set of numbers, a reference to a cell containing either of these, or a formula which results in any of these. Up to 255 values can be provided. |
order |
No | Specifies how to rank the number. | 0 = descending order),1 = ascending order. If absent. 0 is assumed. |
Example
Sample data
| A | B | ||
|---|---|---|---|
| 1 | Score | Player | |
| 2 | 85 | John | |
| 3 | 92 | Sarah | |
| 4 | 78 | Mike | |
| 5 | 95 | Emily | |
| 6 | 85 | David |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Rank a value against a list of numbers, from highest to lowest. | =RANK.EQ(A2, $A$2:$A$6) |
Ranks John's score of 85 in descending order, where the highest score gets rank 1. Since he ties with David, they share the same rank. Result: 3 |
| Rank a value against a list of numbers, from lowest to highest. | =RANK.EQ(A2, $A$2:$A$6, 1) |
Ranks John's score of 85 in ascending order, where the lowest score gets rank 1. Result: 2 |
| Rank a value by first looking it up using a name rather than referencing its cell directly. | =RANK.EQ(INDEX(A2:A6, MATCH("Sarah", B2:B6, 0)), A2:A6) |
Looks up Sarah's score by matching her name, then ranks that score in descending order. Result: 2 |
| Rank a value against a list of numbers, from lowest to highest. | =RANK.EQ(A5, $A$2:$A$6, 1) |
Ranks Emily's score of 95 in ascending order — since it's the highest score in the list, it ranks last. Result: 5 |
| Rank a value against a list of numbers, from highest to lowest. | =RANK.EQ(A6, $A$2:$A$6) |
Ranks David's score of 85 in descending order. Since he ties with John, they share the same rank. Result: 3 |
Notes
- If there are duplicate values in the list, RANK.EQ assigns them the same rank. This will affect the ranking of subsequent values.
- The largest number is ranked 1 in descending order, while the smallest number is ranked 1 in ascending order.
- RANK.EQ is not a dynamic function. If the values in the reference range change, you must recalculate the worksheet to update the rank.
- RANK.EQ cannot handle non-numeric values. If the reference range includes text or logical values, it will return a #VALUE! error.
- If the number to be ranked is not in the reference range, RANK.EQ will return a #N/A error.
Tips
- For percentile rankings, consider using PERCENTILE.EXC or PERCENTILE.INC.
- Use RANK.EQ in combination with functions such as IF to create conditional rankings or groupings.
- To get a unique rank for each value (even duplicates), combine RANK.EQ with functions such as COUNTIF.
- For datasets larger than 255 values, we suggest using helper columns to consolidate values.
- To identify a text value's rank, you can combine RANK.EQ with INDEX and MATCH to link the text with the associated numeric value. (See example above.)