RANK.AVG
The RANK.AVG function assigns a rank to a specified value within a list. It handles ties by assigning the average rank to each tied value. Use this function to determine the relative standing of a value.
Syntax 🔗
=RANK.AVG(number, ref, [order])
number | The value you want to rank. |
ref | An array of values representing the list of values to rank against. |
order (Optional) | A numerical value specifying the ranking order. 0 (descending) is the default, 1 (ascending) for smaller values being ranked higher. |
About RANK.AVG 🔗
Use the RANK.AVG function in Excel to determine the position of a number within a range of values. This function assigns a rank to a specified value in a list, resolving ties by averaging the ranks of the tied values. It's useful for ranking tasks such as evaluating academic or sales performance. Provide the value you want to rank, the reference array of values to compare, and if necessary, specify the ranking order to adjust the sorting behavior. RANK.AVG helps you manage your ranking tasks efficiently.
Examples 🔗
Consider an array of test scores: 85, 78, 92, 85, 78. You want to know the rank of a new score of 85 within this set. Use the RANK.AVG formula like this: =RANK.AVG(85, {85, 78, 92, 85, 78})
Assume you have sales figures for a sales team: $1500, $1900, $1500, $1200. You wish to rank a new sales figure of $1600 within this dataset. Use the RANK.AVG formula like this: =RANK.AVG(1600, {1500, 1900, 1500, 1200}, 1)
Notes 🔗
Ensure the values in the reference array match the format and unit of the number being ranked. Use the optional 'order' argument to decide if higher or lower values receive higher ranks. By default, duplicate values will have the same rank, and their ranks will be averaged.
Questions 🔗
In cases where there are tied values in the reference array being ranked, RANK.AVG resolves ties by assigning the average of the ranks that would have been occupied by those values. This ensures a smooth and equitable ranking process.
Can I change the default ranking order in the RANK.AVG function?Yes, you can alter the ranking order by specifying the optional order argument. Use 0 for a descending order where higher values receive higher ranks, and 1 for an ascending order where lower values receive higher ranks.
No, the RANK.AVG function can handle duplicate values in the reference array. If multiple values are tied, their ranks will be averaged and assigned accordingly.
How accurate are the ranks provided by the RANK.AVG function when handling tiebreakers?The RANK.AVG function maintains high accuracy when dealing with tiebreakers by ensuring that the final rank assigned to tied values is the precise average of the ranks they would have held individually.