PERCENTILE Function in Excel, Google Sheets, Airtable & Notion
PERCENTILE returns the value at a given percentile of a data set, for example the 90th percentile with k = 0.9. Between data points it interpolates.
Syntax in each app
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | PERCENTILE.INC(array, k)PERCENTILE() is the older name and gives the same result. PERCENTILE.EXC uses a different method. |
| Google Sheets | ✔ Built in | PERCENTILE(data, percentile)PERCENTILE.INC and PERCENTILE.EXC are also available. |
| Airtable | ✘ Not available | — Airtable formulas cannot sort a list of values, so there is no way to pick the k-th value or a percentile from a rollup. Calculate percentiles in a spreadsheet, or with an extension or script. |
| Notion | ≈ Workaround | lets(s, list.sort(), r, k * (s.length() - 1), i, floor(r), s.at(i) + (r - i) * (s.at(min(i + 1, s.length() - 1)) - s.at(i)))No percentile function; this formula does the same interpolation as PERCENTILE.INC. Notion's .at() is 0-based, which is exactly what the formula needs. |
| Excel | =PERCENTILE.INC(A2:A6, 0.4) | open excel.new ↗ |
|---|---|---|
| Google Sheets | =PERCENTILE(A2:A6, 0.4) | open sheets.new ↗ |
| Airtable | ✘ No equivalent. Airtable formulas cannot sort a list of values, so there is no way to pick the k-th value or a percentile from a rollup. Calculate percentiles in a spreadsheet, or with an extension or script. | |
| Notion workaround | lets(s, [15, 20, 35, 40, 50].sort(), r, 0.4 * (s.length() - 1), i, floor(r), s.at(i) + (r - i) * (s.at(min(i + 1, s.length() - 1)) - s.at(i))) | |
Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.
Value 15 20 35 40 50
Arguments
| Argument | What it means |
|---|---|
array | The range of numbers. It does not need to be sorted. |
k | The percentile as a decimal from 0 to 1: 0.9 for the 90th percentile, 0.25 for the first quartile. Outside 0–1 gives #NUM!. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=PERCENTILE.INC(A2:A6, 0.4) | 29 | Tester data: position 0.4 × 4 = 1.6, so 60% of the way from 20 to 35. | |
=PERCENTILE.INC(A2:A6, 0.5) | 35 | The 50th percentile is the median. | |
=PERCENTILE.INC(A2:A6, 1) | 50 | k = 1 is the maximum, k = 0 the minimum. | |
=PERCENTILE.INC(A2:A6, 1.2) | #NUM! | k must be between 0 and 1. |
Examples use Excel / Google Sheets syntax unless noted.
How PERCENTILE works
A percentile tells you the value below which a share of the data falls. The 90th percentile of response times is the time that 90% of requests beat; the 25th and 75th percentiles are the quartiles that bound the middle half of the data. Enter k as a decimal (0.9) or as a percentage (90%); =PERCENTILE.INC(A2:A100, 90) is a common mistake and returns #NUM!.
PERCENTILE.INC sorts the values, works out the position k × (n − 1) counting from 0, and interpolates between the two neighbouring values when that position is not a whole number. That is why the result is often a value that does not appear in your data. PERCENTILE.EXC uses k × (n + 1) and excludes the end points, so it gives slightly different answers and errors for k near 0 or 1; most tools, including the plain PERCENTILE function, match the .INC version. QUARTILE.INC(range, 1) is a shortcut for PERCENTILE.INC(range, 0.25). To go the other way, from a value to its percentile, use PERCENTRANK.INC.
Neither Airtable nor Notion has a percentile function. Notion can reproduce PERCENTILE.INC with the lets formula on this page, which sorts the list and interpolates. Airtable cannot sort values inside a formula, so percentiles need a spreadsheet, extension or script.
Frequently Asked Questions
- Should I use PERCENTILE.INC or PERCENTILE.EXC?
- Use PERCENTILE.INC (or plain PERCENTILE) unless a method you are following specifies .EXC. INC matches most other software.
- Why does my percentile return a value that is not in my data?
- PERCENTILE interpolates between the two nearest values when the position falls between them.