The RANK function in Google Sheets is used to determine the rank of a specific value in a given dataset.
Table of Contents
The rules for using the
RANK function in Google Sheets are as follows:
- The values placed in the
RANKfunction 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.
Consider this example.
As one of the organizers of an annual quiz bee competition, you were given the task of creating a spreadsheet that will handle all the computations needed. It should be able to calculate the total points a contestant earns per round and the total score for the whole competition. It should also be able to quickly determine the rankings of the contestants based on the scores.
There are 3 rounds in the competition: easy, average, and difficult. A correct answer in each round corresponds to 5, 10, and 15 points, respectively. Data inputs should only be placing a number 1 to a corresponding cell if the contestant obtained the correct answer for that question. You draft up an example scenario and obtained the following:
You have successfully set up the spreadsheet such that the subtotal score per round and total points per contestant will be automatically computed. Now, the only thing left to do is to find a way to rank these scores automatically. This should allow you to determine the winners almost instantaneously without rearranging the dataset from highest to lowest total scores. How should you go about this problem?
Luckily, Google Sheets has a function that can easily solve this problem: the
RANK function. It will return the rank of a specific value when the dataset is sorted in ascending or descending order.
Now, let’s get to know more about the
RANK function in Google Sheets!
The Anatomy of the RANK Function
So the syntax (the way we write) the
RANK function is as follows:
=RANK(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()this is our
RANKfunction. It returns the rank of a specific value in a dataset. Rankings may be based on ascending or descending order.
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. If
datadoes not contain the
valuein any cell or element, the
RANKfunction will return the #N/A error.
is_ascendingdetermines how values in
datawill be arranged. The square brackets ‘’ indicate that this is an optional parameter. By default, its value is 0 when not specified – indicating
datato be arranged in descending order. If a value of 1 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 Function
Take a look at the example below to see how
RANK functions are used in Google Sheets.
As you can see, the
RANK 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 1. Notice also how equal values in the dataset (the number 9 in cells A3 and A10) are ranked the same, taking the top rank of the entries.
You may make a copy of the spreadsheet using the link I have attached below:
How to Use the RANK Function in Google Sheets
Now, let’s finally use the
RANK 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’ (or ’RANK’, 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 function so make sure to click on the RANK function. Alternatively, you may select the function by pressing the arrow down keys then pressing Enter or Tab to use the function (the currently selected function will be highlighted in grey and have a brief description below).
- Upon selecting a function, a large text box appears that gives details about the function, and how to use the function. In some instances, a blue question mark will appear on the left side of the cell. If you want this text box to appear, simply click this question mark to show the large text box. Clicking on the arrow at the top right-hand corner will minimize the box while clicking on the x mark close the text box and the blue question mark will appear.
- 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 the
- 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.
RANK function could also be used in conjunction with other functions like the ARRAYFORMULA function to make this process even faster and easier.
Using this additional function allows you to only edit the formula in one cell and the remaining cells will be automatically filled up. This also allows you more flexibility to add more values to the table and the ranks will automatically be updated. Feel free to follow the link provided to learn more about this function.
Another useful function is the IFNA function. This is a more specified 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 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 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.
That’s pretty much it. You can now use RANK 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.