How to Use the PERCENTRANK.INC and PERCENTRANK.EXC Functions in Microsoft Excel for Relative Percentile Ranks

When analysing standardized test scores, sales representative quotas, employee compensation bands, or quality control metrics, knowing an absolute score is often less revealing than understanding where that score stands relative to the entire population. While standard ranking functions like RANK.EQ return ordinal position integers (such as 1st or 14th), statistical analysis usually requires relative rank expressed as a percentile between 0 and 1 (or 0% and 100%). Microsoft Excel provides two specialized functions for this purpose: PERCENTRANK.INC (inclusive) and PERCENTRANK.EXC (exclusive).

Inclusive vs Exclusive Percentile Ranking

Understanding the mathematical distinction between inclusive and exclusive percentile calculation is essential for choosing the appropriate function for your analysis:

• PERCENTRANK.INC (Inclusive): Calculates the rank of a value as a percentage of the dataset, where the minimum value in the array receives a rank of 0.0 (0%) and the maximum value receives a rank of 1.0 (100%). The percentile range is closed: \([0, 1]\). This matches the legacy PERCENTRANK function.
• PERCENTRANK.EXC (Exclusive): Calculates rank relative to the dataset while strictly excluding 0% and 100% as possible ranks. The percentile range is open: \((0, 1)\), defined mathematically as \(\frac{k}{N + 1}\). This is widely used in academic research and quality engineering where no single sample point is assumed to represent the true absolute boundary of an infinite population.

Function Syntax and Arguments

Both functions share identical argument signatures:

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

• array: The range or array of numerical data that defines relative standing.
• x: The specific numerical value whose relative percentile rank you want to determine. If \(x\) does not exist explicitly in the array, Excel interpolates between adjacent values.
• significance (optional): An integer specifying the number of significant decimal digits returned in the result. If omitted, Excel defaults to 3 decimal places (e.g. 0.750 or 75.0%).

Practical Step-by-Step Example

Consider an assessment given to ten candidates with the following test scores stored in cells A2:A11:

55, 62, 68, 72, 75, 80, 84, 88, 92, 98

1. Calculating Inclusive Rank with PERCENTRANK.INC

To determine the relative percentile rank of a score of 75:

=PERCENTRANK.INC(A2:A11, 75)

Excel evaluates the four scores below 75 out of the nine intervals and returns:

0.444 (or 44.4%)

If you evaluate the minimum score (55):

=PERCENTRANK.INC(A2:A11, 55)

Output: 0.000 (0.0%)

If you evaluate the maximum score (98):

=PERCENTRANK.INC(A2:A11, 98)

Output: 1.000 (100.0%)

2. Calculating Exclusive Rank with PERCENTRANK.EXC

Applying the exclusive formula to the same score of 75:

=PERCENTRANK.EXC(A2:A11, 75)

Excel calculates the rank using \(N + 1\) weighting \((5 / 11)\) and returns:

0.454 (or 45.4%)

Evaluating the minimum score (55) under exclusive ranking:

=PERCENTRANK.EXC(A2:A11, 55)

Output: 0.090 (9.0%). Notice that the lowest score does not equal zero.

Interpolating Non-Existent Values

One major strength of PERCENTRANK.INC and PERCENTRANK.EXC is their ability to calculate percentiles for scores that do not appear in the original sample:

=PERCENTRANK.INC(A2:A11, 70)

Because 70 falls between 68 and 72, Excel automatically applies linear interpolation, returning an exact calculated percentile rank of 0.388 (38.8%).

Customising Precision with the Significance Argument

For fine financial calculations or large cohorts, adjust the significance argument:

# Return five decimal places of precision
=PERCENTRANK.INC(A2:A11, 84, 5)

Output: 0.66666

Troubleshooting Common Errors

• #N/A Error with PERCENTRANK.EXC: If \(x\) is less than the minimum value or greater than the maximum value in the array, PERCENTRANK.EXC returns #N/A because exclusive bounds cannot interpolate outside the strict \((0, 1)\) interval.
• #NUM! Error: Occurs if significance < 1 or if the supplied array is empty.
• #VALUE! Error: Occurs if non-numeric text strings or invalid cell references are passed into the formula arguments.