QUARTILE function in Google Sheets: how to calculate quartiles, the IQR and outliers
QUARTILE splits a dataset into four parts and returns one of the five boundary values: the minimum, the first quartile, the median, the third quartile or the maximum. This guide shows how to calculate quartiles in Google Sheets, how QUARTILE.INC and QUARTILE.EXC differ, how to get the interquartile range and flag outliers, how to calculate quartiles for a group, and what errors to expect.
To calculate a quartile in Google Sheets, give QUARTILE the data and the number of the quartile:
=QUARTILE(C3:C12, 1)
The formula returns the first quartile of C3:C12, the value below which a quarter of the data falls. The second argument chooses which of five values to return:
| Quartile number | Returns | The same as |
|---|---|---|
| 0 | The minimum | MIN(data) |
| 1 | The first quartile, Q1 | PERCENTILE(data, 0.25) |
| 2 | The median, Q2 | MEDIAN(data) |
| 3 | The third quartile, Q3 | PERCENTILE(data, 0.75) |
| 4 | The maximum | MAX(data) |
QUARTILE syntax
=QUARTILE(data, quartile_number)
- data: the range or array of numbers. It does not have to be sorted.
- quartile_number: 0, 1, 2, 3 or 4.
=QUARTILE(C3:C12, 1)
All five values for one dataset, here eight sales figures:
A quartile is usually not one of the values in the data. When its position falls between two values, the function interpolates: Q1 of the eight figures, 38,150, lies between 35,000 and 39,200.
QUARTILE.INC is the same function under its full name; in our test both returned identical results.
QUARTILE.EXC: the exclusive method
QUARTILE.EXC: the exclusive method
=QUARTILE.EXC(data, quartile_number)
QUARTILE.EXC calculates the position of the quartile differently, in the way PERCENTILE.EXC does. Q1 comes out lower and Q3 higher than with QUARTILE; the median is the same.
=QUARTILE.EXC(C3:C12, 1)
On the ten figures of the screenshots, Q1 is 36,050 with QUARTILE and 34,475 with QUARTILE.EXC.
QUARTILE.EXC accepts only the numbers 1, 2 and 3. With 0 or 4 it returned #NUM! in our test, since the minimum and the maximum are exactly what the exclusive method leaves out.
Use QUARTILE by default, and QUARTILE.EXC when the method you follow asks for it. Keep to one of them within a report: an interquartile range from one and a quartile from the other do not belong together.
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
The interquartile range and outliers
The interquartile range and outliers
The interquartile range, IQR, is the distance between the third and the first quartile. It holds the middle half of the data:
=QUARTILE(C3:C12, 3) - QUARTILE(C3:C12, 1)
A common rule treats values that lie more than 1.5 IQR beyond the quartiles as outliers. The two limits:
=QUARTILE(C3:C12, 1) - 1.5 * (QUARTILE(C3:C12, 3) - QUARTILE(C3:C12, 1))
=QUARTILE(C3:C12, 3) + 1.5 * (QUARTILE(C3:C12, 3) - QUARTILE(C3:C12, 1))
In our test data of ten amounts, Q1 was 1,962.5 and Q3 2,750, so the IQR was 787.5 and the upper limit 3,931.25. One amount, 4,500, was above it. To list such rows, pass the limit to FILTER; to count them, to COUNTIF.
Label each row with its quartile
Label each row with its quartile
To say which quarter each value belongs to, compare it with the three quartiles:
=ARRAYFORMULA(IFS(D3:D12 <= QUARTILE(D3:D12, 1), "Q1", D3:D12 <= QUARTILE(D3:D12, 2), "Q2", D3:D12 <= QUARTILE(D3:D12, 3), "Q3", TRUE, "Q4"))
One formula fills the column with Q1 to Q4. IFS checks the conditions in order, so each value gets the first quarter it fits into.
Quartiles for a group
There is no QUARTILEIF. Filter the data inside the formula instead:
=QUARTILE(FILTER(D3:D11, B3:B11 = "North"), 3)
The older pattern does the same with IF inside ARRAYFORMULA, here with PERCENTILE and the region taken from a cell:
=ArrayFormula(PERCENTILE(IF($B$3:$B$11=$G4, $D$3:$D$11), 0.75))
The condition can be on the numbers themselves. The third quartile of the sales between 30,000 and 55,000:
=ArrayFormula(QUARTILE(IF((C3:C11>=30000)*(C3:C11<=55000), C3:C11), 3))
Multiplying the two conditions means “and”: a row counts only if both are true.
Errors in QUARTILE
⚠️ Error: #NUM! with a quartile number above 4 or below 0.
✅ Solution: Use 0 to 4. A decimal is cut to its whole part: in our test 1.9 was read as 1.
⚠️ Error: #NUM! in QUARTILE.EXC with 0 or 4.
✅ Solution: The exclusive method has no minimum and maximum. Use MIN and MAX, or QUARTILE.
⚠️ Error: #NUM! in QUARTILE.EXC on a very small dataset.
✅ Solution: The exclusive method needs enough values: with two numbers, Q1 returned #NUM! in our test. Use QUARTILE.
⚠️ Error: #NUM! on a range that looks full.
✅ Solution: The range has no numbers. Text is ignored, including numbers stored as text. Convert them with VALUE.
⚠️ Error: The quartiles differ from another tool.
✅ Solution: There are several accepted ways to calculate quartiles. Compare QUARTILE.INC and QUARTILE.EXC; one of them usually matches.
Related Google Sheets functions
Related Google Sheets functions
- PERCENTILE: Any share of the data, not only quarters.
- PERCENTRANK: Where a value stands in the data.
- MAX, MIN and MEDIAN: The ends and the middle.
- AVERAGE functions: The mean, to compare with the median.
- RANK: The position of each value.
- Exploratory data analysis: Quartiles as part of a first look at data.
Quartiles describe the rows in the range, whatever they are
Quartiles describe the rows in the range, whatever they are
Quartiles and outlier limits are computed from every value in the range, so they shift with what the range contains. A partial export, a file cut off at a row limit or a copy from last month each give different quartiles, and a row flagged as an outlier today is ordinary once the missing rows are added.
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. QUARTILE 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
Is There a QUARTILEIF Google Sheets Function?
No, there is no QUARTILEIF function. Filter the data inside the formula: =QUARTILE(FILTER(D3:D11, B3:B11 = "North"), 3) returns the third quartile of the rows where column B is North.
What Is the Quartile Function in Google Sheets?
The QUARTILE function in Google Sheets calculates quartiles of a dataset. It returns values representing Q1 (25th percentile), Q2 (median), and Q3 (75th percentile) for easier analysis of data distribution.
How Do You Find Q1 and Q3 on Sheets?
Use =QUARTILE(range, 1) for Q1 and =QUARTILE(range, 3) for Q3. QUARTILE.INC is the same function. QUARTILE.EXC uses the exclusive method and returns a lower Q1 and a higher Q3.
How Do You Find and Calculate the Quartiles in Google Sheets?
To calculate quartiles in Google Sheets, use QUARTILE.INC or QUARTILE.EXC functions. The formula is =QUARTILE.INC(range, quartile_number) or =QUARTILE.EXC(range, quartile_number) for Q1 (25th percentile), Q2 (50th percentile/median), or Q3 (75th percentile).
What Do Percentiles Mean When Talking About Quartiles?
Quartiles divide data into four equal parts, which align with percentiles in 25% increments. The first quartile is the 25th percentile, the second (median) is the 50th percentile, and the third quartile is the 75th percentile, showing the spread of data in sections.
How can I use the QUARTILE function with an array of values?
Type the numbers in curly braces instead of a range: =QUARTILE({35000, 42500, 27300, 55600}, 2). The result of another function, such as FILTER, can be passed the same way.
What should I do if I get a #NUM! error when using QUARTILE.EXC?
QUARTILE.EXC accepts only the quartile numbers 1, 2 and 3; 0 and 4 return #NUM!. The error also appears when the dataset is too small for the exclusive method or contains no numbers. Use QUARTILE (QUARTILE.INC) for the minimum, the maximum and small datasets.
How do I calculate the median (second quartile) in Google Sheets?
Use =MEDIAN(range). =QUARTILE(range, 2) and =PERCENTILE(range, 0.5) return the same value.
