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.
=PERCENTRANK.EXC (array, y, [significance value])
- y – Value to rank
- Array – Array of data values.
- Significance – Although optional, it is the number of significant digits in the result.
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.
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
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.
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.
- 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.