Need advanced Excel, VBA or SQL support? Outsource your project now!
Connect to our Experts within seconds for a free diagnosis
Our professional experts are available now. Your privacy is guaranteed.

Excel PERCENTRANK.EXC Function

Just as you can use other different functions in Excel, there are ways you can use the PERCENTRANK.EXC Function. In this post, you’ll learn how to use the Excel PERCENTRANK.EXC function with syntax approach.

Syntax 

=PERCENTRANK.EXC (array, y, [significance value])

Where;

  • y – Value to rank
  • Array – Array of data values.
  • Significance – Although optional, it is the number of significant digits in the result.

Explanation

The PERCENTRANK.EXC function in Excel is usually used as a means to interpret standings of a value in standardized tests. It returns the rank of a value in a data set as percentage showing the number of values that are less than or equal to the value.

Example

Figure 1: Excel PERCENTRANK.EXC Function

In figure 1 above, the formula in cell D4 is:

=PERCENTRANK.EXC(data,C4) and it returns 0

* where “data” is C4:C9

Interpolation

Assuming Y is arbitrary that does not exist in the data set, the PERCENTRANK.EXC function can interpolate a value between data points to find the percentile rank.

Example 2

Assuming Y =2  is entered as an argument to the PERCENTRANK function, its percentage is interpolated to the value of 65.4% that exists between the percentile rank of 0.46 and 2.32, which are 28.6% and 71.4% respectively.

Figure 2. Example of PERCENTRANK function

Inclusive vs. Exclusive

In the recent versions of Excel, the PERCENTRANK function has been swapped with two (2) functions: PERECENTRANK.EXC and PERECENTRANK.INC. These two formulas apply the same arguments.


The graph below shows differences percentrank.inc and percentrank.exc with a small dataset:

As the size of the input values increases, the difference between the functions reduces.

Notes

  • When [significance] is not included, PERCENTRANK.EXC returns 3.s.f digits
  • The PERCENTRANK.EXC computes rank as a decimal value
  • The PERCENTRANK.EXC function is not available in the earlier versions of Ms Excel prior to 2010.

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc