Spreadsheet formulas explained, side by side

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

AppSupportSyntax & notes
Excel✔ Built inPERCENTILE.INC(array, k)
PERCENTILE() is the older name and gives the same result. PERCENTILE.EXC uses a different method.
Google Sheets✔ Built inPERCENTILE(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≈ Workaroundlets(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.
Try PERCENTILE with your own values
One value per line, or separate with commas
0.5 = median, 0.9 = 90th percentile
Result (as Excel & Google Sheets return it)
29
Your formula, ready to paste
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

ArgumentWhat it means
arrayThe range of numbers. It does not need to be sorted.
kThe 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

FormulaResultWhyCopy
=PERCENTILE.INC(A2:A6, 0.4)29Tester data: position 0.4 × 4 = 1.6, so 60% of the way from 20 to 35.
=PERCENTILE.INC(A2:A6, 0.5)35The 50th percentile is the median.
=PERCENTILE.INC(A2:A6, 1)50k = 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.

Part of the Statistics & Counting functions. Results in the tester follow Excel and Google Sheets behaviour; always double-check important work in your own file.