How does Excel calculate quartiles?

Question

I have the data set [3,7,9,10].

Excel and Desmos both return the value 8 for the median, which agrees with my understanding of how medians are calculated. Since there are four values, the median is calculated as the mean of the middle two: $\frac{7+9}{2}=8$.

My understanding of the first quartile is that it is the median of the lower half of the data. Which would be the mean of $3$ and $7$, so it should be $5$.

Desmos returns 5 as the first quartile, however Excel returns 6 as the first quartile. I'm using the formula quartile({3,7,9,10},1). Similarly Excel gives 9.25 as the third quartile.

What is Excel doing? I have seen in this answer that a formula for the first quartile is $\frac{1}{4}\cdot(n+1)$, which means I would be looking for the $\frac{5}{4}=1.25^{th}$ data value. However, wouldn't that make the first quartile be $4$ rather than $6$?

Answer 1

There is not a single way of calculating quantiles.

This is a table of quantiles using the nine different methods from your data, using R:

type   0%   25%     50%  75%      100%
 1      3    3       7    9        10
 2      3    5       8    9.5      10
 3      3    3       7    9        10
 4      3    3       7    9        10
 5      3    5       8    9.5      10
 6      3    4       8    9.75     10
 7      3    6       8    9.25     10
 8      3    4.6667  8    9.5833   10
 9      3    4.75    8    9.5625   10

From the look of it, your Excel calculation seems to be type 7, while your Desmos calculation seems to be type 2 (testing a different case shows it may not be type 5). Types 1 and 3 only take values from the input data while type 2 sometimes averages two consecutive values; the other types attempt to interpolate so as to estimate the population quantile from the sample data.