Why does creation of a Q–Q plot in Excel need an adjustment by 0.5?

Question

I am aware that different statistical packages provide Q–Q plots using code or via a black box. For example, minitab with R integration for Q–Q plot from here.

I am trying to do this manually via Excel for a column of data in the hope of understanding the underlying dynamics completely. There is an example available from here. (I'd like to believe that being a university-related website, the method the author proposes is sound.)

Essentially, the data is sorted in ascending order and a rank from 1 to 20 (say, if there are 20 data points) is provided to each of the data points. Then, from the rank, 0.5 is subtracted and divided by the number of data points (20 in this example) to provide the percentile whose corresponding standard normal z variate is calculated via the normsinv function.

Why does the 0.5 needs to be subtracted? Is this because the rank (being discrete and contiguously integer) is being approximated by a continuous distribution (normal) and hence this correction makes it more correct?

If doing this correction of 0.5 makes the Q–Q plot more correct, is there a better most statistically correct/rigorous way to construct a Q–Q plot from scratch without giving it to a statistical package to compute automatically? What precisely are the underlying mathematical formulae to produce the Q–Q plot?

Answer 1

It is to make the distribution symmetrical. The values 0 to 19 are just as reasonable as the values 1 to 20.

If you would have five values you would get quantiles

$$0.1,0.3,0.5,0.7,0.9$$

instead of

$$0.2,0.4,0.6,0.8,1.0 $$

Answer 2

The missing idea here is often called plotting position, which is a precise recipe for the version of cumulative probability to be used. With your example of $20$ data points, plotting against (results for) cumulative probabilities $(1, 2, \dots, 19, 20) / 20$ or $0.05, 0.10, \dots, 0.95, 1$ wouldn't treat the tails symmetrically while plotting against (results for) $(0, 1, \dots, 18, 19) / 20$ or $0, 0.05, \dots, 0.90, 0.95$ would just reverse the problem.

But plotting against (results for) $(1/2, 3/2, \dots, 37/2, 39/2) / 20$ or $(0.025, 0.050, \dots, 0.950, 0.975)$ splits the difference and treats the tails symmetrically. Hence the rule (rank $- 1/2)$ / sample size for plotting positions. It is often attributed to Hazen but the main idea can be found in Galton's work earlier.

This isn't the only solution and indeed there are many others, such as rank / (sample size $+ 1$) or (rank $- 1/3$) / (sample size $+ 1/3$), some with more or less elaborate rationales. There is a rather agitated and even angry little literature about which choice is "best". Almost all choices in current use belong to a family (rank $- a$) / (sample size $- 2a + 1$), so those mentioned correspond in turn to $a = 1/2, 0$ and $1/3$. It shouldn't matter much for graphical purposes which you choose and you could follow personal taste or local tradition or habit.

It gets more subtle. A plotting position of $0$ would be useless for any reference or brand-name distribution without a finite minimum and a position of $1$ would be useless for any such distribution without a finite maximum. So, jumping quickly to your specific application of normal quantile plots, which is a very common example, calculation of expected quantiles for a normal or Gaussian distribution obliges us to have a rule which avoids $0$ and $1$ as plotting positions. That is, in principle, a normal distribution has tails that stretch to negative and positive infinity, which are the values corresponding to cumulative probability 0 and 1 respectively. Clearly we can't plot such values.

The plotting positions are not percentiles, but their positions or labels. Strictly and historically, percentiles are actual or interpolated values on a variable, not the associated cumulative probabilities. Some people talk of percentile ranks, which can be a useful term. It seems more often conventional that percentile ranks are themselves quoted as percentages, but that is the same idea.

Some more details and references can be found here. Translation of the Stata code to your own favourite software if different should be trivial. (Clearly, the OP's question is about Excel, but this answer is directed more widely.)

Answer 3

If you have $n$ values sorted in order with indices $i \in \{1,2,\ldots, n\}$ and $n$ is odd, then using a symmetry argument, you presumably want the middle value with index $i=\frac {n+1}2$ to correspond to the median or $0.5$ quantile, as well as some consistency in the treatment of the smallest and largest values. There are various ways of doing this reasonably sensibly, by using

• $\dfrac{i -\frac12}{n}$, as in your question;
• $\dfrac{i-1}{n-1}$, which would put the the minimum observed value at the $0$ quantile and the maximum at the $1$ quantile, possibly a problem if your theoretical distribution has unbounded support;
• $\dfrac{i}{n+1}$, which would give quantile gaps before the first and after the last as big as the gaps between values elsewhere;
• $\dfrac{i-\frac13}{n+\frac13}$, as an illustration of possible variations on the same theme.

There is not a single correct answer: Wikipedia shows nine approaches available in various computer packages. As a further minor complication, if your indices instead start at $0$, so $j \in \{0,1,\ldots, n-1\}$ with the median having $j=\frac{n-1}2$, then these sensible quantile approaches would become $\frac{j +\frac12}{n}$, $\frac{j }{n-1}$, $\frac{j +1}{n+1}$, $\frac{j+\frac23}{n+\frac13}$ respectively.