The `RANK.AVG` function in Excel is used to determine the rank of a number in a list of numbers. If there are duplicates in the list, `RANK.AVG` will assign the average rank to each of the duplicate numbers.
Here’s how you can use the `RANK.AVG` function:
Syntax
RANK.AVG(number, ref, [order])
- number: The number whose rank you want to find.
- ref: An array or reference to a list of numbers. This is the dataset within which you want to determine the rank of the number.
- order: An optional argument that specifies how to rank the numbers. Use:
- `0` or omit for descending order (i.e., highest number gets rank 1).
- `1` for ascending order (i.e., lowest number gets rank 1).
Example
Let’s say you have the numbers 50, 30, 40, 50, and 20 in cells A1 through A5, and you want to find the rank of the number in A1 using `RANK.AVG`.
- Descending Order (default):
If you want to find the rank of the number in cell A1 (50) in descending order:
=RANK.AVG(A1, A1:A5)
This will return `1.5`, because both instances of 50 in the array have an average rank of 1 and 2.
- Ascending Order:
If you want to rank the numbers in ascending order:
=RANK.AVG(A1, A1:A5, 1)
This will return `4.5`, since 50 is one of the larger numbers in the list.
Notes
- `RANK.AVG` is available in Excel 2010 and later versions. If you have an earlier version, use the `RANK` function, but note that it behaves differently with ties.
- The function does not ignore blank or non-numeric values. Ensure your range only contains valid numbers.
- If you are working with a version of Excel using a different language, the function name may vary.
By using the `RANK.AVG` function, you can effectively rank a data set while handling duplicates gracefully by averaging their ranks.
Unlock Your Potential
Excel
Access
Power BI
