Description
Use this function to calculate the rank of a number within a list of numbers, with ties receiving an average rank. Supported in Chains. Can be used with CHILDREFS.
RANK.AVG returns the statistical rank of a value in a dataset. If there are duplicate values in the list, it assigns the average rank to those values.
Syntax
RANK.AVG(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 list of numbers to be evaluated. | 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) or 1 (ascending). If omitted, it defaults to descending order (0). |
Example
Sample data
| A | B | ||
|---|---|---|---|
1 |
Score | Name | |
2 |
89 | Alice | |
3 |
95 | Bob | |
4 |
95 | Charlie | |
5 |
87 | David | |
6 |
92 | Eve |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Rank a value against a list of numbers, from highest to lowest. | =RANK.AVG(A2, $A$2:$A$6, 0) |
Ranks Alice's score of 89 in descending order, where the highest score gets rank 1. Result: 4 |
| Rank a tied value against a list of numbers, from highest to lowest. | =RANK.AVG(A3, $A$2:$A$6, 0) |
Ranks Bob's score of 95 in descending order. Since it ties with Charlie's score for the top spot, they split ranks 1 and 2 evenly. Result: 1.5 |
| Rank a tied value against a list of numbers, from highest to lowest. | =RANK.AVG(A4, $A$2:$A$6, 0) |
Ranks Charlie's score of 95 in descending order. Since it ties with Bob's score for the top spot, they split ranks 1 and 2 evenly. Result: 1.5 |
| Rank a value against a list of numbers, from lowest to highest. | =RANK.AVG(A5, $A$2:$A$6, 1) |
Ranks David's score of 87 in ascending order — since it's the lowest score in the list, it ranks first. Result: 1 |
| Rank a value typed directly into the formula against a list of numbers, using the default order. | =RANK.AVG(92, $A$2:$A$6) |
Ranks the literal value 92 in descending order (the default when no order is specified). Result: 3 |
Notes
- RANK.AVG assigns the same average rank to duplicate values. For example, if two values tie for rank 2, they both receive a rank of 2.5.
- If you omit the order argument, or set it to 0, RANK.AVG ranks numbers in descending order (the largest number gets rank 1).
- If you set the order argument to 1, RANK.AVG ranks numbers in ascending order (the smallest number gets rank 1).
- RANK.AVG returns the #N/A error value if number is not found in ref.
- Non-numeric values in the
refarray are ignored.
Tips
- Use RANK.AVG when you want to assign average ranks to tied values, which can be useful in competitions or statistical analysis.
- When using RANK.AVG with a column of data, always use absolute references (e.g.,
$A$2:$A$10) to ensure the correct range is used when copying the formula. - Combine RANK.AVG with other functions such as IF or COUNTIF for more complex ranking scenarios.
- To get a unique rank for each value (no averages for ties), use RANK instead.
- For datasets larger than 255 values, we suggest using helper columns to consolidate values.
- Remember that RANK.AVG returns a decimal value for tied ranks, which may need to be rounded for display purposes.