The PERCENTRANK.EXC function in Excel is a powerful statistical tool that determines the relative rank of a specified value within a given data set. By outputting the rank as a percentage, this function enables users to easily interpret how a particular value compares to others in the data set, excluding the boundaries at 0 and 1. This makes it particularly useful for identifying outliers and understanding distributions.

Syntax

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

• array: The range or array of data points where you want to find the rank.
• x: The specific value whose rank you want to determine.
• significance: (Optional) The number of digits for rounding the result, default is 3.

Example #1

=PERCENTRANK.EXC(A1:A10, 5)

In this example, this function evaluates the value 5 within the range A1 to A10 and returns the percentage rank of 5 compared to other values in that range. For instance, if the value 5 ranked at the 30th percentile, the output would be 0.3.

Example #2

=PERCENTRANK.EXC(B1:B20, 15, 2)

This function call assesses the rank of the value 15 in the range B1 to B20 and rounds the result to 2 decimal places. If 15 is at the 60th percentile, the resulting output would be 0.60.

Example #3

=PERCENTRANK.EXC(C1:C15, 20)

Here, the function checks the value 20 within C1 to C15. If 20 holds the 75th percentile, the output will be 0.75, indicating that it’s ranked higher than 75% of the data points.

Error handling

• NUM!: This error occurs when the value “x” is not found in the array or exceeds the maximum or minimum in the dataset.
• N/A: This happens if the array provided does not contain at least one number, making the calculation impossible.
• VALUE!: This error can arise if the parameters provided are of an incorrect type, such as text instead of numbers.

Conclusion

The PERCENTRANK.EXC function is a valuable tool for data analysis in Excel, providing insights into how a specific value ranks within a dataset in terms of percentage. Its ability to exclude the boundaries makes it suitable for accurate assessment in various statistical applications. By understanding its usage and potential errors, users can effectively leverage this function for better data-driven decisions.