Check Sheet Logo
Check Sheet

PERCENTRANK.EXC

The PERCENTRANK.EXC function returns the rank of a specified value in a dataset as a percentage between 0 and 1, exclusive. This is commonly used in statistics to determine the relative standing of a value within a dataset. The function takes three arguments: the dataset as a range or array, the value whose rank is being calculated, and an optional number of significant digits to use in the calculation. If the significant_digits parameter is omitted, the default value of 3 is used.

Usage

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

=PERCENTRANK.EXC(data, value, [significant_digits])
Parameters:
  1. data (required):
    The range or array of data to be analyzed.
  2. value (required):
    The value whose rank is being calculated.
  3. significant_digits (optional):
    The number of significant digits to use in the calculation. If omitted, the default value of 3 is used.

Examples

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

Finding the percentile rank of a test score

Suppose you have a dataset of test scores and you want to know the percentile rank of a particular score. You can use the PERCENTRANK.EXC function to determine this. Simply provide the dataset and the test score as arguments to the function.

Determining the relative standing of a stock price

If you have a dataset of stock prices and you want to know the relative standing of a particular price, you can use PERCENTRANK.EXC. This can be helpful in determining whether a stock is under- or overvalued relative to its historical performance.

Ranking employees by productivity

Suppose you have a dataset of employees and their productivity scores. You can use PERCENTRANK.EXC to determine the percentile rank of each employee's score, allowing you to easily rank them from highest to lowest.

Common Mistakes

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

Incorrect range

One common mistake when using PERCENTRANK.EXC is specifying an incorrect range for the data parameter. Make sure that the range includes all the data you want to include in the calculation.

Incorrect value

Another common mistake is specifying an incorrect value for the value parameter. Make sure that the value is included in the dataset and is spelled correctly.

The following functions are similar to PERCENTRANK.EXC or are often used with it in a formula:

  • PERCENTILE.EXC

    The PERCENTILE.EXC function is a statistical function used to find the percentile rank of a given value in a dataset. It returns the k-th percentile of values in a range, where k is a value between 0 and 1 exclusive. This function is similar to the PERCENTILE function, but excludes the percentile value from the calculation.

  • QUARTILE.EXC

    The QUARTILE.EXC function is a statistical function that returns the exclusive quartile of a dataset, which is the value below which a certain percentage of data falls. This function is most commonly used to determine the quartiles of a dataset, particularly in box and whisker plots.

  • RANK.EQ

    The RANK.EQ function returns the rank of a specified value in a list of numbers. The rank of a number is its size relative to other values in the list. It is often used to determine the relative standing of values in a dataset. The function can be used to rank values in ascending or descending order, and can handle ties in several ways.

  • RANK.AVG

    RANK.AVG is a statistical function that returns the rank of a specified value within a dataset. The rank is determined by comparing the value to the other values in the dataset. This function is commonly used to determine the relative position of a value within a range of values.

Learn More

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