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
RANK
function 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 ourRANK
function. It returns the rank of a specific value in a dataset. Rankings may be based on ascending or descending order.value
is the value whose rank will be determined. This value may be specified in the formula or it may be a cell reference.data
is the array or range that contains the dataset from where the rank of value will be obtained from. Ifdata
does not contain thevalue
in any cell or element, theRANK
function will return the #N/A error.is_ascending
determines how values indata
will be arranged. The square brackets ‘[]’ indicate that this is an optional parameter. By default, its value is 0 when not specified – indicatingdata
to be arranged in descending order. If a value of 1 is used,data
will 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
value
to 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 thedata
parameter.
- To specify the
data
parameter, 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_ascending
parameter blank. Press Enter to finish the formula. To complete the table, simply copy the formula to the other rows.
The 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.
