# How does Excel calculate quartiles?

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$$?

• It looks like it is using $(n-1)/4=0.75$ for the first quantile, and $3(n-1)/4=2.25$ for the third quantile, with zero indexing, so that 3 is the $0$th entry, 7 is the $1$st entry, etc. There are answers to this question here
– Joe
May 25, 2022 at 13:42

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.

• Oh my. Nine different types! Maybe the comment Joe left is the formula for type 7? I would need to look at some other data sets to fully understand the differences between the types that gave the same answer for my four values. May 25, 2022 at 15:03
• @DreiCleaner It may be more enlightening if you use $4,5,6,7$ different values May 25, 2022 at 15:09