PERCENTRANK.EXCfunction 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.
- How to use
- Examples of using
PERCENTRANK.EXCformula not working?
- Similar formulas to
PERCENTRANK.EXC formula with the syntax shown below, it has 2 required parameters and 1 optional parameter:
- data (required):
The range or array of data to be analyzed.
- value (required):
The value whose rank is being calculated.
- significant_digits (optional):
The number of significant digits to use in the calculation. If omitted, the default value of 3 is used.
ExamplesHere are a few example use cases that explain how to use the
PERCENTRANK.EXCformula 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.
PERCENTRANK.EXCnot working? Here are some common mistakes people make when using the
PERCENTRANK.EXCGoogle Sheets Formula:
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.
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.EXCfunction 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
PERCENTILEfunction, but excludes the percentile value from the calculation.
QUARTILE.EXCfunction 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.EQfunction 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.AVGis 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.
You can learn more about the
PERCENTRANK.EXC Google Sheets function on Google Support.