The RANK.EQ function in Google Sheets is used to determine the rank of a specific value in a given dataset.
If there are two or more entries of the same value in the dataset, the ranks of those numbers shall be the same (equal).
Table of Contents
The rules for using the RANK.EQ function in Google Sheets are as follows:
- The values placed in the
RANK.EQfunction should be numeric values. - Values considered should be a part of the dataset specified.
- The function returns an integer corresponding to the specified value’s rank when sorted in ascending or descending order.
The RANK.EQ function is a statistical function much like the RANK function. Both functions determine the rank of a number based on a specified data set and even have the same parameters. What is the difference between the two, you ask? Aside from the accepted values of one parameter, they are essentially the same. You can use either function and end up with the same results.
We recommend using the RANK.EQ function instead as it offers a clearer distinction of what result you want to show if there are entries of the same value. The accepted values of one of its parameters are also more intuitive to use compared to what is acceptable for the RANK function. It also seems that the RANK function is only kept for backward compatibility.
Consider this example.
You are one of the organizers of an annual dance competition. One improvement that was proposed is to automate the calculation process for participants’ scores and determining their rankings. Judges will rate each participant based on a given set of criteria, with each one having a set maximum score. The scores from each judge will then be averaged and the team with the highest score will be declared the winner.
Since you already have experience taking the sum and averages of numbers in Google Sheets, you were able to draft up an example spreadsheet. The spreadsheet and sample scenario you used is shown below.
Sorting the column where the average scores are calculated is one way to determine the rankings, but this requires additional steps during the competition proper. This may lead to problems if you only sort out specific parts of the sheet causing your data to be all mixed up. Therefore, you need to find another way for the rankings to be automatically determined once data has been inputted. How should you go about this problem?
The RANK.EQ function provides a solution to this problem. It returns the rank of a specific value in a given dataset and assigns equal ranks to repeated values if they are present. Ranks may be based on ascending or descending order.
Let’s get to know more about the RANK.EQ function in Google Sheets!
The Anatomy of the RANK.EQ Function
So the syntax (the way we write) the RANK.EQ function is as follows:
=RANK.EQ(value, data, [is_ascending])
Let’s dissect this thing and understand what each of these terms means:
=the equal sign is just how we start any function in Google Sheets.RANK.EQ()this is ourRANK.EQfunction. It returns the rank of a specific value in a dataset. When multiple entries have the same value, their ranks will have equal value.valueis the value whose rank will be determined. This value may be specified in the formula or it may be a cell reference.datais the array or range that contains the dataset from where the rank of value will be obtained from. Ifdatadoes not contain thevaluein any cell or element, theRANKfunction will return the #N/A error.is_ascendingdetermines how values indatawill be arranged. The square brackets ‘[]’ indicate that this is an optional parameter. By default, its value is FALSE when not specified – indicatingdatato be arranged in descending order. If a value of TRUE is used,datawill be arranged in ascending order. This means that the least value will have a rank of 1.
A Real Example of Using the RANK.EQ Function
Take a look at the example below to see how RANK.EQ functions are used in Google Sheets.
As you can see, the RANK.EQ function ranks the highest value in the dataset as 1 when the is_ascending parameter is not specified. If you want the lowest value to be ranked first, then this parameter should be specified as TRUE.
Entries that have the same values are highlighted in the example. Notice how regardless of the order specified, the rank returned is the top rank for these entries. Looking at column B, we have ranks 1, 2, 3, and then three rank 4s proceeded with 7 through 10.
You may make a copy of the spreadsheet using the link I have attached below:
How to Use the RANK.EQ Function in Google Sheets
Now, let’s finally use the RANK.EQ function to solve the example showed earlier.
- Simply click on any cell to make it the active cell. For this guide, I will be selecting U3 where I want to show my result.
- Next, simply type the equal sign ‘=’ to begin the function and then followed by the name of the function which is our ‘rank.eq’ (or ’RANK.EQ’, whichever works). You should find that as you are typing, an auto-suggest box appears with the names of the functions that contain the text that you have typed. The one we want is the RANK.EQ function, so make sure to click on the RANK.EQ function.
- Now, we need to specify what the
valueto be ranked should be. For this example, I will first determine the rank of contestant 1. Select cell T3 and insert a comma ‘,’ to indicate that we would now like to input thedataparameter.
- To specify the
dataparameter, select the array containing the dataset to be considered. These are the values in cells T3:T12. Simply select this range and convert it to an absolute cell reference by pressing F4 on your keyboard once. This allows you to copy the formula down while keeping the same set of values for the data parameter.
- Since we want the highest value to be ranked as the highest, let’s just leave the
is_ascendingparameter blank. Press Enter to finish the formula. To complete the table, simply copy the formula to the other rows.
We have successfully automated the process of determining the rankings for this specific example. Modifications need to be made, however, if the number of contestants changes. Luckily, the RANK.EQ function may be used in conjunction with other functions. Let’s discuss the ARRAYFORMULA and IFNA functions and learn how to use them to easily determine the rank of values regardless of dataset size.
The ARRAYFORMULA function allows you to only edit the formula in one cell and the remaining cells will be automatically filled up. To learn more about this function, feel free to follow the link provided.
The IFNA function is a more specific version of the IFERROR function that allows you to specify a value when a formula returns the #N/A error. We already know that the RANK.EQ function returns the #N/A error when the value specified is not a part of the dataset considered. Therefore, any of the two functions can be used.
When you apply these two functions along with the RANK.EQ function for the example earlier, you should end up with the following table:
As you can see, you only need to input the formula for one cell and the remaining values will automatically be filled. Even though we have selected the entire T column as a part of the data set, an error prompt did not show because of the IFNA function.
Exploring conditional formatting options can also help in improving this spreadsheet. For this example, we can see that there are actually two teams tied for first place. Setting up conditional formatting for duplicate ranks or highlighting the first 3 places are a few options to explore but will no longer be discussed in this article.
That’s pretty much it. You can now use RANK.EQ functions in Google Sheets together with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier.
