VoxVlog

Ranking data can help you to better understand important factors. Luckily, the RANK function in Google Sheets makes it easy to rank data sets. Follow along with this guide to learn how it works.

What Is the RANK Function?

RANK functions return a rank from a data set depending on a specific value within the data set. For example, you may want to find where a student ranked on a test. RANK would be able to do that for you by searching the student's name against the test results.

There are three functions in Google Sheets that allow you to rank data. These include the RANK function, RANK.EQ and RANK.AVG. Although all the functions work similarly, slight changes may make them useful for specific tasks.

The RANK.EQ and RANK functions are equivalent and return the rank for the same values, except when there are duplicate values in the data set. The difference between these two is that RANK is an older function from Excel that may not be compatible with future versions. So, if you need to switch between Excel and Google Sheets a lot, it's best to use RANK.EQ. Yet, it's best to eventually switch to Google Sheets.

RANK.AVG is different as it returns a dataset's average position rather than counting duplicate values separately. Use this if you know your data has unnecessary duplicates you can't delete.

RANK Function Syntax

The RANK function in Google Sheets uses three parameters, two of which are needed for the function to work properly and one optional parameter.

Here is the syntax for the RANK function in Sheets:

=RANK(val, dataset, ascending) 

Here are the parameters used in this formula:

• val: This is the value in the dataset that you want to determine the rank of. If the dataset does not have a numeric value in the specified cells, then the formula will return a #N/A error as it will have invalid data.
• dataset: This is the array or the cell range that contains the data to be considered.
• ascending: This is an optional parameter to define whether the values in the defined dataset are in ascending or descending order. If the parameter is 0 (False), then the largest value in the dataset will have a rank of 1. If this parameter is 1, the smallest value in the data will rank as 1.

If the third parameter is omitted, it will default to FALSE or 0.

The RANK.EQ and RANK.AVG uses the same parameters as the RANK formula. Still, here is the syntax for both of them.

=RANK.EQ(val, dataset, ascending) 

=RANK.AVG(val, dataset, ascending) 

How to Use the RANK Function in Google Sheets

Now that we know the syntax and how this formula works, let's look at how you can use it in your spreadsheets.

Using the RANK Function

Below, we have a dataset containing nine students' names and marks. What we aim to do here is to create a new column that shows the rank of a particular student based on their score. Here are the steps to do so:

Read this guide on Absolute values if you'd like to learn more about why we used them. It's an Excel guide, but it applies to Google Sheets too.

Using the RANK.EQ Function

The RANK.EQ function returns the rank in the dataset, and if there is more than one instance of the same value in the spreadsheet, then the top rank for the entries will be returned. This is basically the same as the RANK function. The example below demonstrates this.

In this example, the spreadsheet has instances of the same value. As can be seen, both of the functions give the same output, which means in most cases, they can be swapped for each other.

Using the RANK.AVG Function

The RANK.AVG formula is a variant of the RANK formula which means that its usage is very similar, and you can apply this formula to your spreadsheet using the same steps we mentioned above. However, the output of this formula is a bit different.

For this example, we applied the RANK.AVG to the same figures as before

Here are the steps to use the RANK.AVG formula in your spreadsheet:

When you apply the function, you will see that the results are similar to the RANK function. However, when the same value exists in multiple cells, the function will take its average rather than apply the same rank to all the cells.

For example, instead of 90 being the number 1 rank, it results in 1.5 as it takes the average between rank 1 and 2 as there are two scores of 90. The mark of 67 is displayed as 6 as it's the average position of 5, 6, and 7. This is different from RANK.EQ, which automatically assigns the lowest possible rank to matching scores (5 in the example).

Using the RANK Function With Arrays

Before, we looked at an example of using the RANK function in an existing dataset. However, we can also use this function inside a single-cell array. Doing this is simple.

You must write the number whose rank you wish to find in the first parameter. For the second one, we will write the numbers in the array. For this particular example, here is the formula that we used:

=RANK(67, {66,88,75,86,67,65,81,90,71}) 

As you can see in the example, the cell shows where 67 ranks in the data set in the same way as if the data were taken from cell references instead of typed directly into the formula.

Organize Your Data With the RANK Function

RANK functions in Google Sheets are bordering on an intermediate skill. If you're having trouble with them, go through the guide again and build your own spreadsheet to actively follow each step. Google Sheets is a practical skill, and just like any other new skill, it sometimes takes time to nail it down. But, keep at it, and you'll be a pro in no time.

ncG1vNJzZmivp6x7rq3KnqysnZ%2Bbe6S7zGisrJ1dp66vt4yfrKebpJ68r3nGqKagpJViwKmxxK2qaA%3D%3D