Ranking data in Google Sheets can sometimes be challenging, especially when dealing with repeated numbers. However, the RANK.AVG function in Google Sheets comes to the rescue!
Similar to the RANK function, the RANK.AVG function can determine the rank of a specified value or values in a range. But here’s the difference – while the RANK function assigns the same rank to repeated numbers, the RANK.AVG function calculates the average of the repeated ranks and returns it. This is particularly useful when you need more precise rankings.
It’s worth noting that Google Sheets doesn’t have a built-in tiebreaker function for ranking. However, don’t worry! I have a custom formula that can help you achieve continuous ranks for repeated numbers.
Syntax and Examples to the Use of the RANK.AVG Function in Google Sheets
To get started with the RANK.AVG function in Google Sheets, you’ll need to understand its syntax. The syntax is as follows:
RANK.AVG(value, data, [is_ascending])
In this syntax, value refers to the specific value for which you want to find the rank in a set of data. Additionally, you can specify whether you want to rank the value in ascending order or descending order by using either TRUE or FALSE.
By default, if you don’t specify the sorting order, the RANK.AVG formula will return the rank in descending order. But don’t worry, we’ll dive into more examples to clarify this.
One important thing to keep in mind is that the value can also be an array. In such cases, you can use the ArrayFormula function along with RANK.AVG.
Examples to the Use of RANK.AVG Function in Google Sheets
Let’s explore a few examples to help you understand how to use the RANK.AVG function in Google Sheets.
Example 1: Basic Formula
In this example, we’ll use the RANK formula instead of the RANK.AVG formula because there are no duplicate values in Column A.
=RANK.AVG(25,A1:A5)But don’t worry, the RANK.AVG formula can return the same result in this case too.
Example 2: Array Formula
In this example, we’ll again use the RANK.AVG formula to achieve the same result.
=ArrayFormula(RANK.AVG(A1:A5,A1:A5))When you examine the ranks, you’ll notice that the highest value in Column A, which is 25, receives the 1st rank.
To reverse the ranking order, simply specify TRUE at the end of the formula:
=ArrayFormula(RANK.AVG(A1:A5,A1:A5,TRUE))Since there are five numbers in Column A, the rank of the value 25 will now be 5th.
Now, let’s move on to an example that involves duplicate numbers in Column A. This will demonstrate the real power of the RANK.AVG function in Google Sheets.
In the earlier example, the numbers 10 and 25 appeared twice. Take a look at their ranks. If you wish, you can find the rank of the value 25 using the non-array formula:
=RANK.AVG(25,A1:A5)The regular RANK formula would assign a rank of 1 to both the values in cells A3 and A4. However, the RANK.AVG formula returns 1.5 for both.
To fully grasp the pattern of Rank Average, create a sample sheet similar to the one above and repeat a number more than twice in Column A.
If the highest number repeats twice, the actual rank will be increased by 0.5. If it repeats three times, the additional increment will be 1, and so on. This pattern continues.
On the other hand, if the lowest number repeats, each repetition will result in a decrease of 0.5 from the original rank.
That’s all you need to know about the RANK.AVG function in Google Sheets! Enjoy exploring its capabilities on your own.
If you want to discover more useful tips and tricks for Google Sheets, visit Crawlan.com – your go-to resource for all things Google Sheets.
