Are you struggling to organize your data in Google Sheets? Look no further! In this article, we will explore the powerful RANK function and its variations available in Google Sheets. With these functions, you can easily determine the ranking of data values in a dataset. Let’s dive in and find out how!
Understanding the RANK Functions
Google Sheets offers three functions for ranking data: RANK, RANK.EQ, and RANK.AVG. These functions provide the ranking (position) of a value within a dataset. While RANK and RANK.EQ are equivalent and display the highest rank for tied values, RANK.EQ is the preferred notation that explicitly differentiates itself from RANK.AVG.
On the other hand, RANK.AVG returns the average rank of tied entries. It provides a fairer representation for situations when multiple values share the same rank.
To help you visualize the concept, let’s consider the following dataset that demonstrates all three ranking functions, with tied values highlighted in yellow:
In this dataset, both the RANK and RANK.EQ functions display tied values with a rank of 5, whereas RANK.AVG displays the average rank of 5.5 (which is the mean of position 5 and position 6).
Now, let’s take a look at the formulas used:
The RANK formula in column C:
=RANK(B2,B$2:B$9,0)And the RANK.EQ formula in column D, which yields the same result:
=RANK.EQ(B2,B$2:B$9,0)Finally, the RANK.AVG formula in column E:
=RANK.AVG(B2,B$2:B$9,0)You can access this example and more in the model provided at the end of this article.
Syntax of the RANK Functions
All three RANK functions have the same syntax and require two mandatory arguments and one optional argument:
value: The value to be ranked.data: A range or an array containing the data used for ranking.[order]: This optional argument determines whether the values should be ranked in ascending or descending order. Use 0 or FALSE to set the largest value as rank 1. Use 1 or TRUE to set the smallest value as rank 1. If this optional argument is omitted, the default behavior is descending order ranking, equivalent to 0 or FALSE, where the largest value is ranked as 1.
Important Considerations for the RANK Formula
It’s essential to note that the value being ranked must be present in the data range or array; otherwise, the formula will return an #N/A error. Take a look at the following example, where the value 63 cannot be ranked as it is not found within the score range:
Additionally, the order of the data does not affect the ranking. The ranking is solely determined based on the values themselves.
How to Use the RANK Function
Let’s consider the following example, where the RANK function calculates the position of each student in a class:
Even though the data is sorted from highest to lowest scores, it is not necessary for the RANK function to work correctly.
The formula used for the RANK function in this example is:
=RANK(B2,B$2:B$9)Note that the third argument has been omitted, indicating that the largest value in the dataset has a rank of 1.
To reverse the ranking, making the smallest value have a rank of 1, you can set the third argument of the RANK function to 1 or TRUE, like this:
=RANK(B2,B$2:B$9,1)Exploring RANK.EQ Function
Using the same dataset, the RANK.EQ function produces the same result as the RANK function above. Take a look:
The formula for RANK.EQ in this case is:
=RANK.EQ(B2,B$2:B$9)Unveiling RANK.AVG Function
Once again, using the same dataset, we can explore the RANK.AVG function, which calculates an average rank for tied values:
In this scenario, the formula for RANK.AVG is:
=RANK.AVG(B2,B$2:B$9)Including Arrays in the RANK Formula
In addition to data ranges, the RANK functions accept arrays created using array literals {...}. Consider the following example, where the same data is used within an array:
{=RANK(B2,{92,75,84,89,92,79,88,82})}Access the RANK Model
To explore this concept further, access the read-only copy of the model. Feel free to make a copy by selecting File > Make a Copy.... If you’re unable to access the model, it may be due to your organization’s Google Workspace settings. Right-click the link and open it in a private browsing window to view it.
Are you hungry for more knowledge? The RANK function is also covered in Lesson 26 of my free “30-Day Advanced Formula Challenges” course. Make sure to check it out!
The RANK, RANK.EQ, and RANK.AVG functions are invaluable tools in Google Sheets’ statistical function family. Learn more about them in the Google Documentation.
Get ready to take control of your data and unlock new possibilities with the RANK functions in Google Sheets. Happy ranking!
