PERCENTILE in WPS Spreadsheet: Interpolated Data Thresholds
Find a percentile value from a small numeric range, reproduce n-minus-one interpolation and distinguish a threshold from a percentile rank.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
PERCENTILE returns a data-value threshold at a supplied percentile position. The percentile input is a fraction such as 0.9, while the result has the same units as the observations. It does not return the percentile rank of a supplied observation.
Syntax and documented interpolation
=PERCENTILE(array, k)
Array is the numeric dataset. WPS documents k from 0 through 1, inclusive, and interpolation when k is not a multiple of 1/(n−1). The original examples use a small nonempty numeric range and interior k values only.
Sort the observations conceptually as x0 through x(n−1). Compute h=(n−1)k. When h lies between consecutive indices, interpolate between the corresponding observations by h’s fractional part. This is the rule corroborated by the WPS example 1, 2, 3, 4 at k=0.9, which returns 3.7.
Original worksheet example
Enter 10, 20, 40, 80 in A2:A5. Use =PERCENTILE(A2:A5,0.75) in C2.
- There are four observations, so h=3*0.75=2.25.
- Zero-based index 2 contains 40 and index 3 contains 80.
- Move one quarter of the gap from 40 to 80: 40+0.25*40=50.
- The independently expected threshold is 50, even though 50 is not itself an observed value.
Do not round k to an integer or multiply it by 100 before passing it to the function. Formatting 0.75 as 75% expresses the same numeric fraction.
Independent controls
| A2:A5 values | k | Expected threshold |
|---|---|---|
| 10, 20, 40, 80 | 0.25 | 17.5 |
| 10, 20, 40, 80 | 0.5 | 30 |
| 10, 20, 40, 80 | 0.75 | 50 |
| 10, 20, 40, 80 | 0.9 | 68 |
| 80, 10, 40, 20 | 0.5 | 30 |
| 10, 10, 20, 40 | 0.5 | 15 |
The unsorted original order does not change the independently ordered-data calculation. Repeated observations remain part of the dataset, as the final row shows; do not deduplicate them unless that is the intended data-cleaning policy.
Use a threshold thoughtfully
For the first dataset, the 90th-percentile threshold is 68. The only observation strictly above 68 is 80. That does not mean exactly 10% of this four-item dataset lies above the threshold: a finite dataset and interpolation need not produce an exact fraction of selected rows.
Specify whether a rule uses greater than or greater than or equal to a threshold, especially when many observations tie. PERCENTILE calculates the threshold; it does not resolve a domain-specific selection policy. The reverse question, the relative standing of a value within data, is a different function contract.
Historical limits and errors
The lesson documents #VALUE! for nonnumeric k and #NUM! for k outside 0 through 1, an empty array, or more than 8191 data points. The 8191 statement belongs to that historical lesson; this guide does not claim a current universal capacity ceiling. Original controls avoid all those boundaries and mixed-type inputs.
The result is not a confidence bound or a prediction of future observations. See AVERAGE for a mean and COUNT for numeric counts; those summaries answer different questions from an interpolated percentile.
Availability and evidence scope
The official WPS lesson is dated 2019-05-22 and does not specify its applicability edition or platform. Its numeric build is unspecified. This documents the function by that date; it does not establish first introduction, the last functioning build, other-platform parity or a retirement/deprecation history.
The complete lesson and its formula images and walkthrough were inspected on October 18, 2026. Original examples are documentation-derived independent calculations, not WPS or other spreadsheet-engine executions.