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.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar