Disclosure (#ABotWroteThis): this post was written by an AI (Claude) at the request of a solo developer in Japan, from the code and the checking scripts the developer and the AI wrote while building a free box-plot tool. Below, "we" means the developer and the AI.
In Japan, quartiles and box plots are part of the junior-high math curriculum, and the textbooks compute quartiles the same way: split the sorted data at the median, leaving the median out when the count is odd, and take the median of each half. Anyone who checks such an answer in Excel can find that QUARTILE disagrees. Whether it disagrees depends on which function you use and on whether the count is odd or even.
- With an odd count (3 or more), the textbook quartiles always equal
QUARTILE.EXC. With an even count they differ, unless the two values on either side of the quartile position happen to be equal -
QUARTILE.INC(the same as Excel's plainQUARTILE) differs from the textbook even for odd counts, again unless the two values around the position are equal
Below, we first check this with formulas. Then we show the results on real municipal statistics for all 47 prefectures of Japan.
Three definitions
Sort the data in ascending order and call the i-th value a_i. The count is n.
The textbook method splits the data in two at the median. When n is odd, the median goes into neither half. The first quartile (Q1) is the median of the lower half, and the third quartile (Q3) is the median of the upper half. This is the method in Japanese junior-high school textbooks, but it is not unique to Japan. Langford's survey of quartile definitions ("Quartiles in Elementary Statistics", Journal of Statistics Education, 2006) calls it the "exclusive" method (his Method 2), and notes that it is the one in Moore and McCabe's textbook, which is why he has seen it called the "M&M method". Wikipedia's article on quartiles lists the same steps as its Method 1 and says the TI-83 calculator's box plot uses it.
One warning about the word "exclusive". It is used in three different senses in this post: Langford's name for the textbook method; Excel's QUARTILE.EXC function (Python's statistics module calls the same formula 'exclusive'); and the "Exclusive median" setting of Excel's box-plot chart. The first is not the same as the second, as the rest of this post shows.
Microsoft's help pages for the two Excel functions (QUARTILE.EXC, QUARTILE.INC) do not give formulas. They only give example values. Here we use the following formulas, which agree both with those examples and with the National Statistics Center's explanation quoted later (for even counts, EXC is off by a quarter of a position).
QUARTILE.EXC takes the value at position (n+1)p, with p = 0.25 for Q1 and p = 0.75 for Q3. If the position is fractional, it interpolates linearly between the two values on either side, in proportion to the fractional part.
QUARTILE.INC takes the value at position 1+(n-1)p. Fractions are handled the same way as EXC.
Let's check against the examples on those help pages. The page for QUARTILE.INC uses 1 2 4 7 8 9 10 12 (8 values), and QUARTILE.INC gives Q1 = 3.5 (position 2.75). With the EXC formula, the same data gives 2.5 (position 2.25), and the textbook method gives 3, the median of the lower half 1 2 4 7. The page for QUARTILE.EXC uses 6 7 15 36 39 40 41 42 43 47 49 (11 values), and QUARTILE.EXC gives Q1 = 15 and Q3 = 43 (positions 3 and 9 by the formula). Since 11 is odd, the textbook method gives the same: 15 is the median of the lower half 6 7 15 36 39, and 43 is the median of the upper half 41 42 43 47 49. INC gives 25.5 and 42.5.
Comparing positions shows when they match
The textbook method can also be expressed as the value at a given position. Write n = 4k + r, where k is a whole number and r is the remainder (0 to 3). For Q1:
| n | Textbook | EXC | INC |
|---|---|---|---|
| 4k | k+0.5 | k+0.25 | k+0.75 |
| 4k+1 | k+0.5 | k+0.5 | k+1 |
| 4k+2 | k+1 | k+0.75 | k+1.25 |
| 4k+3 | k+1 | k+1 | k+1.5 |
For example, with n = 4k+1, the lower half is the 2k values a_1 to a_(2k). Its median is (a_k + a_(k+1))/2, the value at position k+0.5. The EXC position is (4k+2)/4 = k+0.5, the same point.
In short, the textbook Q1 is at position (n+1)/4 when n is odd and (n+2)/4 when n is even.
- Odd: the textbook method and EXC point to the same position, so the values always match
- Even: EXC points a quarter of a position further out (toward the smaller side for Q1) than the textbook. They match only when
a_kanda_(k+1)are equal - INC points further in: half a position for odd
n, a quarter for evenn. Again, they match only when the two values around the position are equal
Q3 is the mirror image, and the same holds.
Counts of 1 and 2 are a separate case. The EXC position for Q1 is below 1 and for Q3 above n, so the formula gives no value. The textbook method cannot form two halves from a single value; with two values, each half has one value, so Q1 = a_1 and Q3 = a_2.
Japan's National Statistics Center describes this property in its tutorial series on SSDSE, a family of standardized regional-statistics tables it publishes (the A table covers municipalities, and is the one used below). Part 1 of the series (in Japanese; translations here are ours) explains, with a figure for 46 values, that for an even count QUARTILE.EXC puts "the first quartile a quarter of a position closer to the minimum, and the third quartile a quarter of a position closer to the maximum". Part 3 says that in that material "the interquartile range is computed with Excel's QUARTILE.EXC function", with a note that for an odd count it matches the textbook.
Part 1 also shows how to get the textbook value in Excel when the count is even. For even n, pass PERCENTILE.EXC the fraction (0.25n+0.5)/(n+1) for Q1 and (0.75n+0.5)/(n+1) for Q3 (for odd n, keep 0.25 and 0.75). Multiplying by (n+1) puts Q1 at position 0.25n+0.5, which is (n+2)/4, the textbook position for even n given above.
Results for 47 prefectures
We checked this with the data behind hakohige, a free web tool that draws box plots of Japanese municipal statistics. The data is the National Statistics Center's SSDSE-A-2026 table, covering 1,741 municipalities. The metric is "elementary schools per 10,000 population", where elementary schools include compulsory-education schools (a Japanese school type that combines elementary and junior high in one school). Total population is from 2020 and school counts from 2024. We dropped the one town with a population of 0 (Futaba in Fukushima, whose residents had all been evacuated after the 2011 nuclear accident), leaving 1,740 municipalities.
For each prefecture we sorted its municipalities' values and computed Q1 and Q3 with all three methods. We did not use Excel itself, but Python with the same definitions (statistics.quantiles with method='exclusive' and 'inclusive') and the JavaScript shown below, and confirmed that the two give the same results.
"Differ" below means that at least one of Q1 and Q3 is different. We counted twice: once on the raw values, and once at the tool's displayed precision (2 decimal places).
| Count | Prefectures | Textbook vs EXC differ (raw / at 2 decimals) | Textbook vs INC differ (raw / at 2 decimals) |
|---|---|---|---|
| Odd | 30 | 0 / 0 | 30 / 29 |
| Even | 17 | 17 / 14 | 17 / 16 |
The raw values behave exactly as the formulas predict: no odd prefecture differed from EXC, and no even prefecture matched it.
At 2 decimals, 3 even prefectures (Ibaraki with 44 municipalities, Kyoto with 26, Kochi with 34) look the same under the textbook method and EXC. In Ibaraki, for example, the two values around the Q1 position are 1.43237 and 1.43260. They are so close that the quarter-position shift moves Q1 by less than 0.0001. The Q3 difference is 0.0004, which also doesn't show at 2 decimals. For INC, Osaka (43 municipalities, odd) and Ibaraki (44, even) look the same at 2 decimals.
Four examples (values rounded to 2 decimal places). The last row is one of the three where the difference hides below the displayed precision:
| Prefecture | Municipalities | Textbook Q1 / Q3 | EXC Q1 / Q3 | INC Q1 / Q3 |
|---|---|---|---|---|
| Miyagi | 35 (odd) | 1.49 / 2.50 | 1.49 / 2.50 | 1.52 / 2.47 |
| Nara | 39 (odd) | 1.35 / 6.53 | 1.35 / 6.53 | 1.36 / 4.82 |
| Wakayama | 30 (even) | 2.61 / 5.18 | 2.59 / 5.39 | 2.61 / 5.07 |
| Ibaraki | 44 (even) | 1.43 / 2.05 | 1.43 / 2.05 | 1.43 / 2.05 |
Nara's Q3 drops from 6.53 to 4.82 under INC. When a quartile falls where the values are far apart, even half a position moves it this much.
In JavaScript
EXC and INC are just "compute the position, then interpolate linearly".
// a is sorted ascending. pos is a 1-based position
const at = (a, pos) => {
const i = Math.floor(pos), f = pos - i;
return f === 0 ? a[i - 1] : a[i - 1] + f * (a[i] - a[i - 1]);
};
// quart is Excel's second argument: 1 = Q1, 2 = median, 3 = Q3 (INC also takes 0 = min and 4 = max; EXC returns #NUM! for those)
const quartileExc = (a, quart) => at(a, (a.length + 1) * quart / 4); // QUARTILE.EXC(range, quart)
const quartileInc = (a, quart) => at(a, 1 + (a.length - 1) * quart / 4); // QUARTILE.INC(range, quart)
With 1 or 2 values, quartileExc returns NaN for quart 1 and 3, because the position falls outside the array. Passing quartileExc a quart of 0 or 4 gives undefined. In both cases Excel returns #NUM!; these two functions do not check their inputs.
The textbook method cuts the data into a lower and an upper half and takes their medians. This is the tool's implementation, trimmed down to return only Q1 and Q3.
const median = (a) => { const m = a.length >> 1; return a.length % 2 ? a[m] : (a[m - 1] + a[m]) / 2; };
function quartiles(values) {
const a = [...values].sort((x, y) => x - y), n = a.length;
if (!n) return null;
const lo = n > 1 ? a.slice(0, n >> 1) : a, hi = n > 1 ? a.slice((n + 1) >> 1) : a;
return { q1: median(lo), q3: median(hi) };
}
>> 1 divides by 2 and rounds down. The lower half, a.slice(0, n >> 1), is the first floor(n/2) values; the upper half, a.slice((n + 1) >> 1), starts right after the ceil(n/2)-th value. When n is odd, these two cuts leave the median out of both halves. The n > 1 ? ... : a part is the tool's own rule: with a single value, that value becomes Q1 and Q3. The textbook procedure doesn't define this case, so this is not a value the textbook method gives.
Libraries mostly default to the INC formula. d3-array's d3.quantile uses the method known as R-7 (the seventh of the nine quantile definitions catalogued by Hyndman and Fan), which is the same formula as QUARTILE.INC; so do the defaults of NumPy's percentile, pandas' quantile, and R's quantile. Python's statistics.quantiles is the exception: its default method is 'exclusive', which is the EXC formula.
When someone says "your answer is wrong"
Say a student, a colleague, or a reviewer compares your spreadsheet against an answer worked out the textbook way and the quartiles don't match. Check two things first.
- Was the function
QUARTILE.INC(orQUARTILE), orQUARTILE.EXC? - Is the count odd or even?
With QUARTILE.INC, expect a difference whatever the count. Google Sheets has the same pair of functions; in its function list, the description of QUARTILE.INC simply refers to QUARTILE.
Excel's built-in box and whisker chart has a "Quartile Calculation" setting, described in Microsoft's help. "Inclusive median" is described as "The median is included in the calculation if N (the number of values in the data) is odd", and "Exclusive median" as "The median is excluded from the calculation if N (the number of values in the data) is odd". Leaving out the median for odd counts is what the textbook does, so with an odd count, "Exclusive median" gives the textbook quartiles. Part 1 of the National Statistics Center's series picks this setting in its charting steps (listed under its Japanese label), and notes that the chart's quartiles are the values of the QUARTILE functions, so they "do not always agree with the textbook"; its own example has an odd count of 47. We have not run the chart on an even count. If it follows QUARTILE.EXC, expect the same quarter-position shift.
With QUARTILE.EXC and an odd count, the result should match the textbook. If it doesn't, suspect something else, such as the range you selected. With QUARTILE.EXC and an even count, expect a difference. It is a quarter of a position, and depending on the displayed precision it may not be visible. If you need the textbook value for an even count as well, use the PERCENTILE.EXC fractions given above.
The tool whose data this post uses, hakohige (Japanese for "box-and-whisker"), computes quartiles with the textbook method. Pick a prefecture, and it shows a box plot and the quartiles for its municipalities. The link below is the English interface.
Top comments (1)
This is the kind of mismatch that makes students think they got it wrong when the spreadsheet is using a different definition. A small toggle in the tool that shows "textbook" vs "Excel EXC" vs "Excel INC" side by side would turn the confusion into a lesson about how quartiles are defined.