PERCENTRANK.EXC function

The PERCENTRANK.EXC function in Excel is used to calculate the relative percentile rank of a value in a data set, excluding the 0th and 100th percentiles. It tells you what percentage of the data values fall below a specific value (or how the value compares to the rest of the data), while excluding the extreme ends of the distribution.

Syntax:

PERCENTRANK.EXC(array, x, [significance])

Arguments:

• array: Required. The array or range of data values. This should contain the data you want to analyze.
• x: Required. The data value for which you want to calculate the relative percentile rank.
• significance: Optional. The number of significant digits to return in the result. If omitted, the default is 3 digits of precision.

Output:

The function returns the relative percentile rank of the value x within the given data array. This rank is expressed as a decimal between 0 and 1, representing the percentage of data points that fall below the value x. The result excludes the 0th and 100th percentiles.

How It Works:

• PERCENTRANK.EXC calculates the relative rank of a value x in the dataset by comparing it to the rest of the data, but excluding the 0th and 100th percentiles.
• The result represents the proportion of the data that is below the given value. For example, if the result is 0.75, it means the value is higher than 75% of the data points.

Example 1: Finding the Percentile Rank of a Value

Suppose you have a data set in cells A1:A10:

5, 10, 15, 20, 25, 30, 35, 40, 45, 50

To find the percentile rank of the value 25 in this dataset, use the formula:

=PERCENTRANK.EXC(A1:A10, 25)

This will return the percentile rank of 25 as a decimal value. For this example, it will return a value close to 0.25, indicating that 25 is higher than 25% of the data points, excluding the extreme values.

Example 2: Finding the Percentile Rank of the Maximum Value

Suppose you want to calculate the percentile rank of the maximum value in the dataset. In this case, the maximum value is 50.

Use the formula:

=PERCENTRANK.EXC(A1:A10, 50)

This will return a value close to 1, indicating that 50 is higher than 100% of the data points, excluding the extreme ends (0th and 100th percentiles).

Example 3: Specifying the Significance Level

You can specify the number of decimal places you want the result to have. For instance, if you want the result to be rounded to 2 decimal places, use the formula:

=PERCENTRANK.EXC(A1:A10, 25, 2)

This will return the percentile rank of 25 with 2 decimal places of precision.

Key Points:

• PERCENTRANK.EXC calculates the relative rank of a value in a data set, excluding the 0th and 100th percentiles.
• The function returns a value between 0 and 1. The result tells you what percentage of the data falls below the given value.
• The significance argument is optional, and it allows you to control the number of decimal places returned.

Use Cases:

• Statistics: Find the percentile rank of specific values in a dataset, excluding extreme values.
• Risk Management: Identify how a particular value compares to the rest of the data (e.g., in a financial analysis).
• Education: Calculate the rank of a student’s score relative to the rest of the class.
• Market Research: Determine how a product’s price compares to the range of prices in a market.

Notes:

• PERCENTRANK.EXC excludes the 0th and 100th percentiles, which means the ranks are calculated for the middle range of data, excluding the extreme values.
• If you need to include the 0th and 100th percentiles, use PERCENTRANK.INC instead.