PERCENTRANK.EXC
ThePERCENTRANK.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.
- How to use
PERCENTRANK.EXC
formula? - Examples of using
PERCENTRANK.EXC
formula PERCENTRANK.EXC
formula not working?- Similar formulas to
PERCENTRANK.EXC
Usage
Use the 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.
Examples
Here are a few example use cases that explain how to use thePERCENTRANK.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.
Related Formulas
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 thePERCENTILE
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.