When analyzing test scores, employee performance benchmarks, customer lifetime value, or sales quotas, raw numbers often fail to convey where an individual observation stands relative to the overall group. While the standard RANK function yields absolute positions (such as 1st, 5th, or 42nd), comparing rank across datasets of varying sizes is awkward. In statistical analysis, calculating the percentile rank—the relative percentage of scores in a dataset that fall below a specific value—provides a standardized, normalized metric.
In Google Sheets, analysts compute percentile ranks using two dedicated statistical formulas: PERCENTRANK.INC (Inclusive) and PERCENTRANK.EXC (Exclusive). Understanding the mathematical distinctions between inclusive and exclusive boundaries is critical for reporting accurate psychometric, educational, and corporate metrics. This comprehensive guide covers their syntax, mathematical formulas, interpolation methods, and real-world worked examples.
PERCENTRANK.INC vs PERCENTRANK.EXC: The Key Differences
Both functions return the relative standing of a specific value (\(x\)) within a data array as a percentage ranging between 0 and 1 (0% to 100%). However, they handle dataset endpoints and boundary distributions differently:
- PERCENTRANK.INC (Inclusive): Evaluates rank over a closed interval of \([0, 1]\). The minimum value in the dataset always receives an exact percentile rank of 0 (0%), and the maximum value always receives an exact rank of 1 (100%). This is identical to the legacy
PERCENTRANKfunction and is the standard metric used in general business, sales, and employee evaluations. - PERCENTRANK.EXC (Exclusive): Evaluates rank over an open interval of \((0, 1)\). It excludes 0% and 100% as attainable ranks, establishing boundaries at \(\frac{1}{N + 1}\) and \(\frac{N}{N + 1}\). This method is widely preferred in formal academic research, biostatistics, and psychometrics to avoid overstating sample extremes.
Syntax and Parameter Rules
The PERCENTRANK.INC Syntax
=PERCENTRANK.INC(data, value, [significance])The PERCENTRANK.EXC Syntax
=PERCENTRANK.EXC(data, value, [significance])Argument Specifications
data: Required. The array or cell range containing the numeric dataset (e.g.,A2:A50). Text strings and blank cells in the range are automatically ignored.value: Required. The specific number whose percentile standing you want to determine. The value does not need to be an exact member of the dataset; Google Sheets automatically interpolates intermediate values.[significance]: Optional. The number of significant digits to which the returned decimal percentage is rounded. If omitted, Google Sheets defaults to 3 significant figures (e.g.,0.750or 75.0%).
Mathematical Formulations
Assume an ordered dataset of \(N\) values where \(k\) represents the zero-based rank of value \(x\) (the count of values strictly less than \(x\)):
Inclusive Formula (PERCENTRANK.INC)
$$\text{Percentile Rank} = \frac{k}{N – 1}$$
Exclusive Formula (PERCENTRANK.EXC)
$$\text{Percentile Rank} = \frac{k + 1}{N + 1}$$
If the target value \(x\) does not match an exact number in the dataset, Google Sheets applies linear interpolation between the two adjacent surrounding values.
Step-by-Step Worked Example
Consider a sales team evaluation dataset where 5 representatives achieved the following quarterly sales totals (in thousands of pounds):
| Cell | Sales Representative | Sales (£000s) |
|---|---|---|
A2 |
Alice | 10 (Minimum) |
A3 |
Bob | 20 |
A4 |
Charlie | 30 |
A5 |
David | 40 |
A6 |
Emma | 50 (Maximum) |
Here, sample size \(N = 5\).
Comparing Output Results Across All Values
Let us calculate both inclusive and exclusive ranks for each data point and an intermediate unlisted score (25):
| Target Value (\(x\)) | PERCENTRANK.INC Formula | Inclusive Result | PERCENTRANK.EXC Formula | Exclusive Result |
|---|---|---|---|---|
| 10 (Min) | =PERCENTRANK.INC(A2:A6, 10) |
0.000 (0.0%) | =PERCENTRANK.EXC(A2:A6, 10) |
0.166 (16.7%) |
| 20 | =PERCENTRANK.INC(A2:A6, 20) |
0.250 (25.0%) | =PERCENTRANK.EXC(A2:A6, 20) |
0.333 (33.3%) |
| 25 (Interpolated) | =PERCENTRANK.INC(A2:A6, 25) |
0.375 (37.5%) | =PERCENTRANK.EXC(A2:A6, 25) |
0.416 (41.7%) |
| 30 | =PERCENTRANK.INC(A2:A6, 30) |
0.500 (50.0%) | =PERCENTRANK.EXC(A2:A6, 30) |
0.500 (50.0%) |
| 40 | =PERCENTRANK.INC(A2:A6, 40) |
0.750 (75.0%) | =PERCENTRANK.EXC(A2:A6, 40) |
0.666 (66.7%) |
| 50 (Max) | =PERCENTRANK.INC(A2:A6, 50) |
1.000 (100.0%) | =PERCENTRANK.EXC(A2:A6, 50) |
0.833 (83.3%) |
Notice that for the median score (30), both methods converge at exactly 0.500 (50%). However, at the extremes, PERCENTRANK.INC scales completely from 0% to 100%, whereas PERCENTRANK.EXC scales between \(\frac{1}{6} \approx 16.7\%\) and \(\frac{5}{6} \approx 83.3\%\).
Applying ArrayFormulas for Automated Column Calculations
Manually dragging formulas down a large spreadsheet is inefficient. You can evaluate an entire column of student scores or KPIs using MAP and LAMBDA:
=MAP(B2:B100, LAMBDA(score, IF(ISBLANK(score), "", PERCENTRANK.INC(B$2:B$100, score, 2))))Format the calculated column as a percentage via Format > Number > Percent to present values as 25.00%, 50.00%, and 100.00% cleanly.
PERCENTRANK vs PERCENTILE: Complementary Functions
Do not confuse PERCENTRANK with PERCENTILE:
PERCENTRANK(data, value): Takes a value and returns its percentile rank (e.g., “A score of 85 corresponds to the 78th percentile”).PERCENTILE(data, k): Takes a percentile rank (\(k\)) and returns the corresponding data value (e.g., “The 78th percentile score is 85”).
Troubleshooting Common Errors
| Error Code | Underlying Cause | Recommended Solution |
|---|---|---|
#NUM! in PERCENTRANK.EXC |
Value is outside the strict open interval (less than min or greater than max). | PERCENTRANK.EXC cannot evaluate values strictly outside the range boundaries. Use PERCENTRANK.INC instead. |
#N/A |
The data range contains zero numeric entries or is empty. | Ensure the input range references numeric data and does not contain solely text strings. |
Result shows 0 or 1 instead of percentage |
Standard decimal cell formatting | Highlight the cells and select Format > Number > Percent from the Google Sheets menu. |