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 |
|---|---|
| Score | Name |
| 89 | Alice |
| 95 | Bob |
| 95 | Charlie |
| 87 | David |
| 92 | Eve |
Sample formulas
| Formula | Description | Result |
|---|---|---|
=RANK.AVG(A2, $A$2:$A$6, 0) |
Ranks Alice's score (89) in descending order. | 4 |
=RANK.AVG(A3, $A$2:$A$6, 0) |
Ranks Bob's score (95) in descending order. | 1.5 |
=RANK.AVG(A4, $A$2:$A$6, 0) |
Ranks Charlie's score (95) in descending order. | 1.5 |
=RANK.AVG(A5, $A$2:$A$6, 1) |
Ranks David's score (87) in ascending order. | 1 |
=RANK.AVG(92, $A$2:$A$6) |
Ranks the score 92 in descending order (default). | 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.
Related functions
AVERAGE
AVERAGEA
AVERAGEIF
AVERAGEIFS
LARGE
MAX
MAXA
MAXIFS
MEDIAN
MIN
MINA
MINIFS
PERCENTILE
PERCENTILE.EXC
PERCENTILE.INC
QUARTILE
QUARTILE.EXC
QUARTILE.INC
RANK
RANK.AVG
RANK.EQ
SMALL
STDEV
STDEV.P
STDEV.S
STDEVA
STDEVPA