Use =QUARTILE(B2:B9,1) to find the first quartile in Google Sheets. Change the second argument to 2 for the median or 3 for the third quartile. The result can fall between actual observations.
Find the First Quartile (Q1)
Enter this dataset starting in A1. Leave cells marked “(blank)” empty.
| Student | Score |
|---|---|
| A | 55 |
| B | 60 |
| C | 68 |
| D | 72 |
| E | 80 |
| F | 85 |
| G | 90 |
| H | 95 |
Enter this formula in E2:
=QUARTILE(B2:B9,1)The first quartile is 66. It falls between 60 and 68 rather than matching a score in the dataset. You do not need to sort the source cells first.
QUARTILE Syntax and Quartile Numbers
=QUARTILE(data, quartile_number)data is the numeric range. The second argument selects one of five cutoffs:
| Quartile number | Meaning | Result for B2:B9 |
|---|---|---|
| 0 | Minimum | 55 |
| 1 | First quartile | 66 |
| 2 | Median | 76 |
| 3 | Third quartile | 86.25 |
| 4 | Maximum | 95 |
Use integer codes from 0 to 4. In the live test, 1.5 was truncated to 1, while 5 returned #NUM!. Do not use a fractional code to request another percentile.
Find the Median and Third Quartile
=QUARTILE(B2:B9,2)This returns 76, halfway between the two middle scores, 72 and 80. MEDIAN returns the same middle value without a quartile-number argument.
=QUARTILE(B2:B9,3)This returns 86.25. QUARTILE uses the inclusive percentile method, which can interpolate between observations. The upper cutoff does not guarantee that exactly 75% of a small sample lies at or below it.
Repeated values also make literal “bottom quarter” or “top quarter” counts unreliable. Use the cutoff to describe the distribution; inspect ties before using it to assign a fixed number of people or records to groups.
Build a Five-Number Summary
=QUARTILE(B2:B9,0)
=QUARTILE(B2:B9,4)Enter these in separate cells. They return 55 and 95, matching MIN and MAX. Combine them with Q1, median, and Q3 to summarize the dataset’s center and spread.
Calculate the Interquartile Range
=QUARTILE(B2:B9,3)-QUARTILE(B2:B9,1)The interquartile range is 20.25: 86.25 minus 66. It measures the distance between the lower and upper quartile cutoffs, rather than the full minimum-to-maximum range.
Choose the Inclusive or Exclusive Method
=QUARTILE.INC(B2:B9,1)
=QUARTILE.EXC(B2:B9,1)Entered separately, the formulas return 66 and 62. Plain QUARTILE matches QUARTILE.INC. QUARTILE.EXC uses a different percentile-position calculation; choose the method required by your analysis and apply it consistently.
For a cutoff other than the quartiles, use PERCENTILE. Do not replace a quartile code with 0.25 expecting the first quartile; the second argument is a code, not a percentage.
Calculate a Quartile for Matching Rows
Enter this dataset starting in A25. Leave cells marked “(blank)” empty.
| Region | Value |
|---|---|
| East | 10 |
| West | 20 |
| East | 30 |
| East | 50 |
=QUARTILE(FILTER(B26:B29,A26:A29="East"),1)The result is 20, calculated from East values 10, 30, and 50. FILTER creates the subset first because QUARTILE has no separate condition argument.
Text and logical values in the tested array were ignored. Still inspect source data: a score accidentally stored as text can disappear from the calculation without producing an error.
Other Google Sheets articles you may also like