Description
Use this function to determine the rank of a number in a list of numbers. Supported in Chains. Can be used with CHILDREFS.
RANK is useful for finding the relative standing of a value within a dataset.
Syntax
RANK(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 | The range of numbers to which the number is to be compared. |
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 | A number specifying how to rank the number. |
A value of 1 for ascending order, or 0 for descending order. If omitted, "0" is used. |
Example
Sample data
A |
B |
|
|---|---|---|
1 |
10 |
3 |
2 |
20 |
19 |
3 |
15 |
George |
4 |
25 |
45 |
5 |
30 |
-8 |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Rank a value against a rectangular range of numbers, from highest to lowest, ignoring any text cells. | =RANK(A2, A1:B5) |
Ranks the value in A2 (20) in descending order among the 9 numeric values in A1:B5, skipping "George" in B3 since it's text. Result: 4 |
| Rank a value against a single column of numbers, from lowest to highest. | =RANK(A3, A1:A5, 1) |
Ranks the value in A3 (15) in ascending order among the 5 values in column A only. Result: 2 |
| Rank a value typed directly into the formula against a rectangular range, ignoring any text cells. | =RANK(25, A1:B5) |
Ranks the literal value 25 in descending order among the 9 numeric values in A1:B5. Result: 3 |
| Rank a value against a rectangular range of numbers, from lowest to highest, ignoring any text cells. | =RANK(B5, A1:B5, 1) |
Ranks the value in B5 (-8) in ascending order among the 9 numeric values in A1:B5 — since it's the smallest, it ranks first. Result: 1 |
Notes
- If two or more numbers in the list are the same, they will have the same rank. However, this will affect the ranks of subsequent numbers.
- Non-numeric values (empty cells, text values, and logical values) in
refare ignored. - If a cell in the range
refhas an error, that error will be returned. - The order argument is optional. If omitted, RANK results use descending order.
- Use RANK.EQ to resolve ties by assigning the average rank to each tied value.
Tips
- RANK can be used with COUNT to handle tie values and adjust ranks accordingly.
- RANK is useful in creating rankings for performance, sales, or other measurable data sets.
- For datasets larger than 255 values, we suggest using helper columns to consolidate values.
- You can combine RANK with other functions such as IF, INDEX, and VLOOKUP to create dynamic reports and dashboards.