13
$\begingroup$

I am interested in the definition of quartile that is usually used when you're in basic statistics. I have a Stat 101 type book and it just gives an intuitive definition. "About one quarter of the data falls on or below the first quartile..." But, it gives an example where it calculates Q1, Q2, and Q3 for the set of data

5, 7, 9, 10, 11, 13, 14, 15, 16, 17, 18, 18, 20, 21, 37

Since there are 15 pieces of data, it chooses 15 as the median, Q2. It then splits up the remaining data into two halves, 5 through 14, and 16 through 37. These each contain 7 pieces of data and they find the median of each of these sets, 10 and 18, as Q1 and Q3, respectively. This is how I would calculate it myself.

I looked at Wikipedia's article and it gives 2 methods. One agrees with the above, and one says you could also include the median 15 in both sets (but you wouldn't include the median if it was the average of the two middle numbers in the case of an even number of data points). This all makes sense to me.

But, then I checked Excel to see how Excel calculates it. I am using Excel 2010, which has 3 different functions. Quartile was available in 2007 and previous versions. It seems they want you to stop using this in 2010 but it's still available. Quartile.Inc is new but agrees exactly with Quartile as far as I can tell. And, there is Quartile.Exc as well. Both of the last 2 are new in 2010 I believe. This time, I just tried using the integers 1, 2, 3, ..., 10. I'm expecting Excel to give median of 5.5, Q1 of 3, and Q3 of 8. The method from the statistics book, as well as both methods on Wikipedia would give these answers, since the median is the average of the middle two numbers. Excel gives

quartile number, Quartile.Inc, Quartile.Exc
1,               3.25,         2.75 
2,               5.5,          5.5
3,               7.75,         8.25

Neither of these agree with what I have previously talked about.

The descriptions in the help file for Excel are:

Quartile.Inc - Returns the quartile of a data set, based on percentile values from 0..1, inclusive.

Quartile.Exc - Returns the quartile of the data set, based on percentile values from 0..1, exclusive.

Can any one help me understand this definition Excel is using?

Improve this question
$\endgroup$
2
  • 7
    $\begingroup$ Another great illustration of why not to use Excel for anything statistical. :-) $\endgroup$
    – Wayne
    May 9, 2012 at 19:39
  • 2
    $\begingroup$ Friends don't let friends use Excel for statistics. Sad but true $\endgroup$
    – Chris Beeley
    May 11, 2012 at 12:52

6 Answers 6

Reset to default
14
$\begingroup$

Typically, a rank $r$ (between $1$ and $n$ for $n$ data) is converted to a percent $p$ via the formula

$$p = 100\frac{r-\alpha}{n+1-2\alpha}$$

for some predetermined "plotting position" $\alpha$ between $0$ and $1$, inclusive. Solving for $r$ in terms of $p$ gives

$$r = (n+1-2\alpha) (p/100) + \alpha.$$

Excel has historically used $\alpha=1$ for its PERCENTILE and QUARTILE functions. The online documentation for QUARTILE.INC and QUARTILE.EXC is useless, so we have to reverse-engineer what these functions are doing.

For example, with data $(1,2,3,4,5,6,7,8,9,10)$, we have $n=10$ and $p \in \{25, 50, 75\}$ for the three quartiles. Using $\alpha=1$ in the preceding formula yields ranks of $9(0.25)+1 = 3.25$, $9(0.50)+1 = 5.5$, and $9(0.75)+1 = 7.75$, reproducing the results for QUARTILE.INC.

If instead we set $\alpha=0$ the corresponding ranks are $11(0.25) = 2.75$, $11(0.50) = 5.5$, and $11(0.75) = 8.25$, reproducing the results for QUARTILE.EXC.

Further testing on your part (I do not have a recent version of Excel) may establish the validity of my guess that these two versions of the quartile function are determined by these two (extreme) values of $\alpha$.

By the way, fractional ranks are converted into data values by means of linear interpolation. The process is explained and illustrated in my course notes at Percentiles and EDF Plots--look near the bottom of that page. There is also a link to an Excel spreadsheet illustrating the calculations.

If you would like to implement a general percentile function in Excel, here's a VBA macro to do it:

'
' Converts a percent, computed using plotting position constant A,
' into a percent appropriate for the Excel Percentile() and
' Quartile() functions.  (The default value of A for Excel is 1;
' most values in use are between 0 and 0.5.)
'
Public Function PercentileA(P As Double, N As Integer, A As Double) As Double
    If N < 1 Or A < 0# Or A > 1# Or P < 0# Or P > 1# Then
        Exit Function
    End If
    If N < 2 Then
        PercentileA = 0.5
    Else
        PercentileA = ((N - 2 * A + 1) * P + A - 1) / (N - 1)
    End If
End Function

It converts a nominal percent (such as 25/100) into the percent that would cause Excel's PERCENTILE function to return the desired value. It is intended for use in cell formulas, as in =PERCENTILE(Data, PercentileA(0.25, Count(Data), 0.5)).

Improve this answer
$\endgroup$
4
  • $\begingroup$ Note that once you do understand exactly what Excel is doing, you can use it effectively for statistical work. $\endgroup$
    – whuber ♦
    May 9, 2012 at 20:50
  • 6
    $\begingroup$ If I may humorously disagree with your comment: Excel can be used effectively for statistical work, if you're a statistical wizard who can prove from first-principles what should be done, then reverse-engineer Excel's methods to determine what it's actually doing. When you're that good, most any tool will do. Though I'd also point out that in this thread, 100% of the wizards involved in this discussion so far do not have access to the latest version of Excel, so are unlikely to actually use it. $\endgroup$
    – Wayne
    May 9, 2012 at 21:31
  • $\begingroup$ Touché, @Wayne. (But some of us still use older versions of Excel :-).) $\endgroup$
    – whuber ♦
    May 9, 2012 at 21:32
  • 1
    $\begingroup$ Whuber, thanks for sharing the VBA solution. This will be extremely helpful. As someone who needs to do Stats but is stuck with Excel as the only readily available tool (yes, I've tried R, but can't quite get my head around it), I appreciate tools to help bend Excel to my needs. $\endgroup$
    – dav
    May 14, 2012 at 17:00
4
$\begingroup$

It appears to me that Excel's quartile.inc agrees with the original quartile, which agrees with R's default and other definitions.

With a helpful hint from whuber, I found that Excel's quartile.exc seems to agree (on the 1..10 case) with R's type=6 definition of quantile:

   > For types 4 through 9, Q[i](p) is a continuous function of p, with
    > gamma = g and m given below. The sample quantiles can be obtained
    > equivalently by linear interpolation between the points (p[k],x[k])
    > where x[k] is the kth order statistic. Specific expressions for p[k]
    > are given below.
    > 
    > ...
    > 
    > 
    > Type 6 m = p
    >       .p[k] = k / (n + 1). Thus p[k] = E[F(x[k])].
    >       This is used by Minitab and by SPSS.

Which apparently makes the answer to your question: "Yes, Minitab and SPSS do."

Improve this answer
$\endgroup$
2
  • $\begingroup$ Doesn't R have nine definitions of quantiles? (+1 for the edit, btw) $\endgroup$
    – whuber ♦
    May 9, 2012 at 19:53
  • $\begingroup$ @whuber: Pay no attention to the man behind the curtain! (I'll edit my response. On further examination, it does match one of R's other definitions, which is evidently what Minitab and SPSS use. Thanks!) $\endgroup$
    – Wayne
    May 9, 2012 at 19:54
2
$\begingroup$

I think the exc flavor of quartile is just ignoring the 5 and the 37 (min and max in your original data).

In Stata, both the default and alternate versions give you quartile.exc values with this data.

Improve this answer
$\endgroup$
5
  • $\begingroup$ This guess appears to be inconsistent with the documentation which asserts that the max and min can indeed be returned by QUARTILE.EXC. $\endgroup$
    – whuber ♦
    May 9, 2012 at 21:11
  • $\begingroup$ In my version of Excel 2010, the QUARTILE.EXC(cell range,k) will return #NUM! unless k={1,2,3}, which correspond to 25th, 50th, and 75th percentiles according to the pop-up menu that appears. The original QUARTILE will also accept 0 and 4 as the second argument, which correspond to the min and the max. $\endgroup$
    – dimitriy
    May 9, 2012 at 22:45
  • 1
    $\begingroup$ The documentation states "If quart ≤ 0 or if quart ≥ 4, QUARTILE.EXC returns the #NUM! error value." That seems true. The second statement "MIN, MEDIAN, and MAX return the same value as QUARTILE.EXC when quart is equal to 0 (zero), 2, and 4, respectively" appears false unless I am missing something. What a mess! $\endgroup$
    – dimitriy
    May 9, 2012 at 22:49
  • $\begingroup$ +1 Thank you for checking into this, Dimitriy! Indeed, the only difference between my guess and yours (which effectively turns $n$ into $n-1$ and subtracts $1$ from each rank) is that my formula really should return the min and max for the 0 and 100 percentiles, respectively, rather than #NUM!, so it sounds like your characterization is the better one (yet my characterization provides justification for yours). I wonder what Excel's current PERCENTILE function does? :-) $\endgroup$
    – whuber ♦
    May 10, 2012 at 14:37
  • 1
    $\begingroup$ The 3 flavors of percentile behave the same way as quartile for me. For the 5-37 data, PERCENTILE.EXC(range,k) gives #NUM! for k={0,1}. For k=0.25, PERCENTILE.EXC gives 10. If I throw away 5 and 37, it gives 10.5, which agrees with the other 2 ways. $\endgroup$
    – dimitriy
    May 10, 2012 at 15:34
2
$\begingroup$

Lots of interesting detailed stuff but to get back to the original question I don't see that two slightly different ways that might not give exactly the same answer really matters. The first quatile is the point at which 25% of the observations fall at or below it. Depending on your sample size that may or may not be an exact point in the data. So if one point is below and the next is above, this first quartile is not really well defined and any point in between these two can serve equally well. The same is true for the median when the sample size is even. The rule picks the midpoint between the data points below and above. But nothing really says that the choice given by the rule is really any better than any other point.

Improve this answer
$\endgroup$
1
  • $\begingroup$ +1. However, I would like to suggest this reasoning, although appropriate for some applications, may be a little too limited for general purposes: some choices of plotting position ($\alpha$ between $1/3$ and $1/2$, usually) provide slightly better values for probability plotting, for instance. This isn't going to be relevant to computing quartiles, as you note, but it will be relevant to computing the more extreme percentiles. Computing percentiles is so closely related to quartile computation (I'm sure it's the same underlying code) that this "detailed stuff" is worth bearing in mind, IMHO. $\endgroup$
    – whuber ♦
    May 10, 2012 at 14:42
0
$\begingroup$

For those of you who do use Excel there is quite a good breakdown of the different version methods here http://peltiertech.com/WordPress/comparison/

Improve this answer
$\endgroup$
1
  • 2
    $\begingroup$ It would be better if you could summarize them in your answer. Whilst the link may answer the question they sometimes get out of date in which case your answer will not be helpful for future readers. $\endgroup$
    – Andy
    Aug 19, 2015 at 14:34
0
$\begingroup$

in excel 2016 i noticed, that one can obtain right values of quartiles if:

  • data set has odd number of entries: use QUARTILE.EXC
  • data set has even number of entries: use average of QUARTILE.EXC and QUARTILE.INC
Improve this answer
$\endgroup$
1
  • 1
    $\begingroup$ Given how many definitions of quantiles there are as evidenced in some of the other answers can you explain what you mean by the right values? $\endgroup$
    – mdewey
    Sep 30, 2018 at 16:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.