Check Sheet Logo
Check Sheet

RANK

The RANK function returns the rank of a specified value within a dataset. The rank of a value is its size relative to other values in the dataset. The function can be used to determine the rank of a single value or an entire dataset. The rank can be calculated in ascending or descending order. This function is commonly used in data analysis to determine the relative position of a value within a set of values.

Usage

Use the RANK formula with the syntax shown below, it has 2 required parameters and 1 optional parameter:

=RANK(value, data, [is_ascending])
Parameters:
  1. value (required):
    The value whose rank is to be determined. This can be a number, reference to a cell containing a number, or an array of numbers.
  2. data (required):
    The dataset in which the rank of the value is to be determined. This can be a range of cells or an array of numbers.
  3. is_ascending (optional):
    An optional parameter that specifies whether the rank should be calculated in ascending or descending order. The default value is TRUE, which calculates the rank in ascending order.

Examples

Here are a few example use cases that explain how to use the RANK formula in Google Sheets.

Ranking sales figures

A company wants to rank their sales representatives based on their sales figures over the past quarter. The RANK function can be used to determine the rank of each representative's sales figure compared to their peers.

Ranking test scores

A teacher wants to rank their students based on their test scores. The RANK function can be used to determine the rank of each student's score compared to their peers.

Ranking sports teams

A sports analyst wants to rank the top teams in a league based on their win-loss records. The RANK function can be used to determine the rank of each team's record compared to their competitors.

Common Mistakes

RANK not working? Here are some common mistakes people make when using the RANK Google Sheets Formula:

Forgetting to specify the 'data' parameter

One common mistake when using the RANK function is forgetting to specify the 'data' parameter. This can cause the function to return an error or an incorrect result.

Using the 'is_ascending' parameter incorrectly

Another common mistake when using the RANK function is using the 'is_ascending' parameter incorrectly. If you set this parameter to 'FALSE' when the values in your dataset are sorted in ascending order, or vice versa, the function will return an incorrect result.

Related Formulas

The following functions are similar to RANK or are often used with it in a formula:

  • AVERAGE

    The AVERAGE function calculates the average (arithmetic mean) of the values passed to it. It is commonly used to find the average of a range of cells containing numerical data.

  • MEDIAN

    The MEDIAN function returns the median (middle) value of a set of numbers. It is commonly used to find the middle value in a range of data points. If the number of data points is even, it returns the average of the two middle values. This function can be useful in statistical analysis and data visualization.

  • MODE

    The MODE formula in Google Sheets returns the most frequently occurring value in a dataset. It is commonly used in statistical analysis to identify the value that occurs most frequently in a set of data. The formula requires at least one input value, but can accept multiple values to include in the analysis.

  • PERCENTILE

    The PERCENTILE function returns the value at a given percentile of a dataset. This can be useful in statistics and data analysis when trying to find the value that corresponds to a certain percentile in a set of data.

  • QUARTILE

    The QUARTILE function in Google Sheets is used to calculate quartiles from a given data set. Quartiles divide a dataset into four equal parts. This function can be used to find the value at a given quartile rank or to calculate quartile ranges.

Learn More

You can learn more about the RANK Google Sheets function on Google Support.