A to Z of Excel Functions: The PERCENTRANK.EXC Function

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the PERCENTRANK.EXC function.

The PERCENTRANK.EXC function

The Excel PERCENTRANK.EXC function calculates the relative position, between zero [0] and one [1] but with the extreme values excluded, of a specified value within a supplied array.  It calculates the percentage rank of a value in an ordered dataset, i.e. it is the percentage of values in the dataset that are less than or equal to itself.

The function first occurred in Excel 2010.

The syntax of the PERCENTRANK.EXC function is as follows:

It contains three arguments:

• array: this is required and represents the array of values within which you want to find the relative position of a specific value (i.e. the array or range of numerical values that defines relative standing)
• x: also required, this is the value that you want to calculate the relative position of (x must be within the range of the values in the supplied array, but it does not need to be exactly equal to one of the values: if x is not found in the array, the array values are interpolated to calculate the percentage rank)
• significance: this is an optional argument that specifies the number of significant digits that the returned percentage value is rounded to (by default, this is rounded to three [3] decimal places).

This function is particularly useful when you want to determine the percentile rank of a value, excluding the highest and lowest values in the dataset.

It should be noted that:

• if array is empty, PERCENTRANK.EXC returns the #NUM! error value
• if significance < 1, PERCENTRANK.EXC returns the #NUM! error value
• if x does not match one of the values in array, PERCENTRANK.EXC interpolates to return the correct percentage rank
• if you want to include the highest and lowest values in the dataset use PERCENTRANK.INC instead.

Please see my examples below: 

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every business day.

A full page of the function articles can be found here.