PERCENTILE function in Google Sheets: PERCENTILE.INC and PERCENTILE.EXC with examples
PERCENTILE returns the value below which a given share of a dataset falls: the 90th percentile is the value that 90% of the data does not exceed. This guide covers the syntax, how PERCENTILE.INC and PERCENTILE.EXC differ and why they return different numbers, percentiles for a filtered group, finding the top 10%, and the errors the functions return.
PERCENTILE returns the value below which a given share of the data falls:
=PERCENTILE(C3:C12, 0.9)
The formula returns the 90th percentile of C3:C12: the value that 90% of the numbers do not exceed. The share is written as a number from 0 to 1, so 0.9, or 90% with the percent sign, and not 90.
PERCENTILE syntax
=PERCENTILE(data, percentile)
- data: the range or array of numbers.
- percentile: the share, from 0 to 1.
=PERCENTILE(C3:C12, 0.25)
Three functions share this syntax:
- PERCENTILE and PERCENTILE.INC are the same calculation. In our test they returned identical results.
- PERCENTILE.EXC uses a different method and returns different numbers except at the median.
Some fixed points help to check a result: =PERCENTILE(data, 0) is the minimum, =PERCENTILE(data, 1) the maximum, and =PERCENTILE(data, 0.5) the median. The 25th, 50th and 75th percentiles are the quartiles: =QUARTILE(data, 1) equals =PERCENTILE(data, 0.25).
PERCENTILE.INC and PERCENTILE.EXC
PERCENTILE.INC and PERCENTILE.EXC
Both sort the data and find a position in it; when the position falls between two values, they interpolate. They differ in how the position is calculated for n values:
- INC: position = percentile × (n − 1) + 1. The percentile can be anything from 0 to 1.
- EXC: position = percentile × (n + 1). The percentile has to be between 1/(n + 1) and n/(n + 1); outside this band the function returns #NUM!.
On the ten sales figures of the screenshots, the two methods give:
| Percentile | PERCENTILE.INC | PERCENTILE.EXC |
|---|---|---|
| 0.25 | 36,050 | 34,475 |
| 0.5 | 43,900 | 43,900 |
| 0.75 | 54,400 | 56,200 |
| 0.9 | 58,270 | 60,430 |
=PERCENTILE.EXC(C3:C12, 0.25)
=PERCENTILE.INC(C3:C12, 0.75)
=PERCENTILE.EXC(C16:C25, 0.75)
=PERCENTILE.INC(C3:C12, 0.9)
=PERCENTILE.EXC(C16:C25, 0.9)
EXC pushes the low percentiles lower and the high ones higher. The gap is widest at the ends and on small datasets, and shrinks as the dataset grows.
Which one to use
- Use PERCENTILE (INC) by default. It works for every percentile from 0 to 1.
- Use PERCENTILE.EXC when a method or a standard you follow asks for the exclusive calculation, and the dataset is large enough for the percentile you need. With ten values, the 95th percentile does not exist for EXC.
- Do not mix them in one report. A 90th percentile from one sheet and from another are comparable only if both used the same function.
OWOX Data Marts
See your first report built in real time. 15 minutes.
- Connect your data warehouse
- Pick your metrics
- Get a live Google Sheets report
In the time it takes to write a ticket. Then imagine never writing that ticket again.
Book a DemoWe'll use your actual use case
What the functions ignore
In our test, a range that included a column of text returned the same result as the numbers alone: text and empty cells are skipped. A range with no numbers at all returns #NUM!.
Percentile of a group
To calculate a percentile only for the rows that meet a condition, filter the data first:
=PERCENTILE(FILTER(D3:D12, C3:C12 = "Sarah"), 0.5)
FILTER passes only the amounts of one sales rep to PERCENTILE. The same works with IF inside ARRAYFORMULA: =ARRAYFORMULA(PERCENTILE(IF(C3:C12 = "Sarah", D3:D12), 0.5)). Both returned the same value in our test.
The condition can be on the numbers themselves. This formula takes the third quartile of the sales of 40,000 and more:
=QUARTILE(FILTER(C3:C, C3:C>=40000), 3)
Rows above or below a percentile
Rows above or below a percentile
Everything up to the 70th percentile
=FILTER(B2:C, C2:C<=PERCENTILE(C2:C, 0.7))
The top 10%
The 90th percentile is the threshold of the top 10%:
=FILTER(B3:C12, C3:C12 >= PERCENTILE(C3:C12, 0.9))
To count them instead: =COUNTIF(C3:C12, ">=" & PERCENTILE(C3:C12, 0.9)). In our test of ten values, one was at or above the threshold.
Highlight them
In Format > Conditional formatting, choose Custom formula is and enter, for a range that starts in C3:
=C3 >= PERCENTILE($C$3:$C$12, 0.9)
The opposite question
PERCENTILE turns a share into a value. To go from a value to its share, which is where a number stands in the data, use PERCENTRANK. To get its position in order, use RANK.
Errors in PERCENTILE
⚠️ Error: #NUM! with a percentile such as 90.
✅ Solution: The percentile is a share from 0 to 1. Write 0.9 or 90%.
⚠️ Error: #NUM! in PERCENTILE.EXC with a small or large percentile.
✅ Solution: EXC works only between 1/(n + 1) and n/(n + 1). With ten values, 0.05 returned #NUM! in our test, and so did 0. Use PERCENTILE.INC, or more data.
⚠️ Error: #NUM! on a range that looks full.
✅ Solution: The range holds no numbers: they are stored as text, often after an import. Convert them with VALUE.
⚠️ Error: Two sheets show different percentiles for the same data.
✅ Solution: One uses INC and the other EXC, or one range includes rows the other does not. Check both formulas.
⚠️ Error: The result is a number that is not in the data.
✅ Solution: That is expected. When the position falls between two values, the function interpolates between them.
Related Google Sheets functions
Related Google Sheets functions
- QUARTILE: The 25th, 50th and 75th percentiles by number.
- PERCENTRANK: The share of the data below a value.
- MAX, MIN and MEDIAN: The ends and the middle of the data.
- RANK: The position of a value in order.
- FILTER: Rows that meet a condition.
- COUNTIF and COUNTIFS: Count the values beyond a threshold.
A percentile is only as good as the rows it was computed on
A percentile is only as good as the rows it was computed on
A percentile describes a whole distribution, so it reacts to what is in the range: a sample of the orders, an export capped at a row limit or last month’s copy each give a different 90th percentile, and nothing in the cell says which rows were used.
The OWOX Data Marts extension for Google Sheets covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas.
What changes for the person who maintains the sheet:
- Refresh replaces re-export. Refresh current report in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it.
- The source is written on the sheet. The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report.
- No SQL and no warehouse access needed. The analyst decides which data marts and columns are available. You choose from that list.
The extension does not write or change the analyst’s SQL. PERCENTILE in the sheet then runs on the complete, current rows the query returns. The extension comes with the OWOX Cloud plans of OWOX Data Marts; spreadsheet reporting describes both ways data reaches a sheet.
Turn your data into decisions.
Governed data marts give you the clean foundation ML needs to actually work.
- No AI hallucinations
- Analyst-governed definitions
- Every number traces to SQL
Frequently Asked Questions
What are common errors when using the PERCENTILE function, and how can I fix them?
#NUM! appears when the percentile is outside 0 to 1 (for example 90 instead of 0.9), when the range contains no numbers, and in PERCENTILE.EXC when the percentile is outside the band from 1/(n+1) to n/(n+1). Text and empty cells in the range do not cause an error; they are ignored.
How does the PERCENTILE function handle empty cells and text values?
The PERCENTILE function ignores empty cells and text values, calculating the percentile based only on numeric data.
Is the PERCENTILE function accurate for small datasets?
The accuracy may vary for small datasets, especially when using PERCENTILE.EXC, which excludes the 0th and 100th percentiles, making it less suitable for small data samples.
Can the PERCENTILE function help identify outliers in data?
Yes. A common rule uses the quartiles: values below Q1 - 1.5 x IQR or above Q3 + 1.5 x IQR are treated as outliers, where Q1 = PERCENTILE(data, 0.25), Q3 = PERCENTILE(data, 0.75) and IQR = Q3 - Q1. A simpler cut flags everything above the 99th or below the 1st percentile.
How do I calculate the top 10% of values using the PERCENTILE function?
Use PERCENTILE(data_range, 0.9) to calculate the value that represents the top 10% of your dataset.
How can I use the PERCENTILE function with an array of values?
You can use the PERCENTILE function with an array by referencing the range containing the array values. This function will calculate based on the range provided, analyzing the distribution or ranking of the data.
What is the difference between PERCENTILE.EXC and PERCENTILE.INC?
They calculate the position of the percentile differently. PERCENTILE.INC uses percentile x (n - 1) + 1 and accepts any percentile from 0 to 1. PERCENTILE.EXC uses percentile x (n + 1) and works only between 1/(n+1) and n/(n+1). EXC returns lower values for low percentiles and higher values for high ones; at the median the two agree. PERCENTILE is the same as PERCENTILE.INC.
