Excel quartile functions and box and whisker plot show wrong results for quartiles

Question

Excel quartile functions and box and whisker plot show wrong results for quartiles. Try for example with the dataset: 5, 6, 8, 9, 12, 12, 13, 13. According to the Excel’s box and whisker plot the 1. and 3. quartiles are 6.5 and 12.75 if you exclude median. Including median gives still wrong results. The correct values are 7 and 12.5. Both Geogebra and the site WolframAlpha show the correct results. See https://www.wolframalpha.com/input/?i=%7B5%2C+6%2C+8%2C+9%2C+12%2C+12%2C+13%2C+13%7D

Answer 1

As the percentile wiki page states:  There is no standard definition of percentile.

The Excel PERCENTILE help page __used_to__ explain Excel's method of interpolation.  Unfortunately, that has been scrubbed as MSFT continues to "sanitize" its help pages, removing useful details. (sigh)

I don't know if there is another MSFT document that explains the details.

Answer 2

Apparently, Excel's B&W plot uses QUARTILE.EXC.

The values that you call "correct" are returned by QUARTILE.INC.

Formulas:

C2: =PERCENTILE($A$1:$A$8, 25%)

D2: =PERCENTILE.INC($A$1:$A$8, 25%)

E2: =QUARTILE($A$1:$A$8, 1)

F2: =QUARTILE.INC($A$1:$A$8, 1)

C3: =PERCENTILE($A$1:$A$8, 75%)

D3: =PERCENTILE.INC($A$1:$A$8, 75%)

E3: =QUARTILE($A$1:$A$8, 3)

F3: =QUARTILE.INC($A$1:$A$8, 3)

D6: =PERCENTILE.EXC($A$1:$A$8, 25%)

F6: =QUARTILE.EXC($A$1:$A$8, 1)

D7: =PERCENTILE.EXC($A$1:$A$8, 75%)

F7: =QUARTILE.EXC($A$1:$A$8, 3)

Answer 3

Thanks for your reply. Math teachers do not accept Excel's results and use other programs such as GeoGebra when making box plots.