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

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

The PERCENTRANK.INC function

The Excel PERCENTRANK.INC function calculates the relative position, between zero [0] and one [1], including the extreme values, 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.INC 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.INC returns the #NUM! error value
• if significance < 1, PERCENTRANK.INC returns the #NUM! error value
• if x does not match one of the values in array, PERCENTRANK.INC interpolates to return the correct percentage rank
• PERCENTRANK. INC calculates a value in the range zero [0] to one [1] inclusive, whereas the PERCENTRANK. EXC function calculates a value in the range between, but excluding, these values.

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.