How to use the PERCENTRANK.EXC Function

In this article, we will learn about how to use the PERCENTRANK.EXC function in Excel.

The PERCENTRANK.EXC is a statistical function. In statistics, finding a percentage or percentile for the number on an array of numbers. The function checks the place of value on the list of values and returns its percentage place on the list of values.

PERCENTRANK.EXC function takes the range of values as array and value. And returns the interpolate percentile for the given value.

Syntax:

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

array : a list of numbers

x : number or value

[significance] : value upto decimal places. Default is 3.

Example:

Let’s understand this function using it as an example.

Here we have a set of values as data ( A1 : A23 ) and we need to find some PERCENTRANK.EXC values for the values. [significance] is set to default which is 3.

Use the formula:

= PERCENTRANK.EXC ( data , D3)

data - array given using named range.

D3 - value to which percentile is calculated.

The function returns the percentage for the value ( 23 ) PERCENTRANK.EXC value or minimum value for which the percentile is 0 .

Now copy the formula to get different PERCENTRANK.EXC values of the range to the remaining cells and formula as shown in the snapshot below.

As you can see the results in the snapshot above.

Here are some of the observations gathered using the PERCENTRANK.EXC function.

Note:

The function returns #VALUE! Error if the argument to the function is non numeric.
The function returns #NA! error if the value of x is less than the minimum value of the array or greater than the maximum value of the array.
The function returns #NUM! error if the value of significance is less than 1.
Significance value 1 returns 1 integer digit result like ( 0 ) but for the value 2 it returns the value upto 2 decimal places like (0.02)
The array argument to the function ignores text, Logic values or Date & Time values.

Hope you understood How to use PERCENTRANK.EXC function in Excel. Explore more articles on statistical function like SMALL function & LARGE function here. Please state your query in the comment box below.

Related Articles

How to use the QUARTILE function in Excel

How to use the LARGE function in Excel

How to use the SMALL function in Excel

How to use Wildcards in Excel

Popular Articles

Edit a dropdown list

If with conditional formatting

If with wildcards

Vlookup by date