Search code examples
excelstatisticsquartile

different strategies for finding Quartile in excel

let us consider following data

9
5
3
10
14
6
12
7
14

i would like to find `Q1,Q2,Q3,let sort data

3
5
6
7
9
10
12
14
14

in excel we can calculate it very easily

=QUARTILE(A2:A10,1)

=QUARTILE(A2:A10,2)

=QUARTILE(B2:B10,3)

results are

6
9
12

but if we calculate by hand, we will get following results

5.5
9
13

why is result so different?thanks in advance

Solution

  • The definition of Quartile is not unequivocally. So there are multiple methods to calculate the Quartile. See https://en.wikipedia.org/wiki/Quartile

    In Excel there are multiple Quartile functions now, see https://support.office.com/en-us/article/QUARTILE-function-93cf8f62-60cd-4fdb-8a92-8451041e1a2a?ui=en-US&rs=en-US&ad=US

    QUARTILE and QUARTILE.INC uses Method 2 while QUARTILE.EXC uses Method 1.