The short explanation is that Excel is calculating the quartiles as percentiles. This is really quite different from the way we ordinarily think of quartiles (as medians of the upper/lower half of the data). Here's a quick explanation of how Excel does what it does, using your data as an example. I can't be 100% sure this is the exact algorithm Excel uses, but this will give the same results.

**Excel assigns PERCENTILES to each value in the array.**

P(4) = 0; P(6)=0.20; P(8)=0.40; ... ; P(16)=1

**Excel then checks where the requested percentile falls in the array.** For Q1, 0.25 falls between 6 and 8.

**Excel then linearly interpolates between these values based on the percentile.**

0.25 percentile is 0.05 percentile higher than 0.20 percentile.

0.05/(P(8)-P(6)) = 0.05/0.20 = 1/4

So, the 25th percentile is 1/4 of the way between 6 and 8. Thus, 6.5 is the returned value. (I realize you typed 5.5, but I checked your data in Excel, and 6.5 is returned quartile. Likewise, 13.5 is returned for Q3 instead of 14.5.)

This of course is a strange way of calculating a quartile and is not to be found on the Wikipedia page about quartiles.

Now for finding a quartile the way you want to -- I have two suggestions.

**Try the Statistics Package Add-in.** I don't have it installed here on my work computer, but it's worth a shot to see if it returns quartile values different than those returned by the worksheet function.

**You can use a hacked-together stand-in formula.** It's messy, but I think it captures what you're looking for.

For Q1, you can use:

```
=IF(ISEVEN(ROUNDDOWN(COUNT(A1:A8)/2,0)),AVERAGE(SMALL(A1:A8,ROUNDDOWN(COUNT(A1:A8)/2,0)/2),SMALL(A1:A8,ROUNDDOWN(COUNT(A1:A8)/2,0)/2+1)),SMALL(A1:A8,ROUNDUP(ROUNDDOWN(COUNT(A1:A8)/2,0)/2,0)))
```

For Q3, you can use:

```
=IF(ISEVEN(ROUNDDOWN(COUNT(A1:A8)/2,0)),AVERAGE(LARGE(A1:A8,ROUNDDOWN(COUNT(A1:A8)/2,0)/2),LARGE(A1:A8,ROUNDDOWN(COUNT(A1:A8)/2,0)/2+1)),LARGE(A1:A8,ROUNDUP(ROUNDDOWN(COUNT(A1:A8)/2,0)/2,0)))
```