How to match Stata percentile calculation in Excel formula?

Question

Stata uses the method of quantile calculation called R-2 (https://en.wikipedia.org/wiki/Quantile), whereas Excel uses R-7 with percentile.inc function. My goal is to find a correct formula in Excel that would give results identical to ones in Stata with the R-2 method.

For now, I can see that percentile.inc matches Stata results only for odd and discrete samples (I am dealing with discrete samples). However, the issue occurs with even samples shown here

Conceptually, using percentile.inc in Excel does not seem to be correct since it is an R-7 method, even though it matches with the R-2 method for odd and discrete samples.

My question is what is the simplest formula that would be correct to use in Excel to match Stata percentile results?

question from:https://stackoverflow.com/questions/66065513/how-to-match-stata-percentile-calculation-in-excel-formula

Answer 1

So a fairly literal translation of R-2 into Excel for N=4 would look like this (assuming sorted data):

=(INDEX(A$2:A$5,CEILING(C2*4,1))+INDEX(A$2:A$5,FLOOR(C2*4+1,1)))/2

It does indeed go wrong if you try and put in a quantile of zero so that would have to be a special case as would a quantile of 1. I assume Stata gives the lowest and highest values in the set in these two cases?

A more dynamic formula with all the checking would look like this:

=IFS(OR(C2<0,C2>1),"Out of range",C2=0,A$2,C2=1,INDEX(A:A,COUNT(A:A)+1),TRUE,(INDEX(A$2:INDEX(A:A,COUNT(A:A)+1),CEILING(C2*COUNT(A:A),1))+INDEX(A$2:INDEX(A:A,COUNT(A:A)+1),FLOOR(C2*COUNT(A:A)+1,1)))/2)

although you could make it shorter using the Let construct in Microsoft 365.

It would probably be nice to implement this as function in VBA which would sort the data as well as returning the quantile value or of course you could do the sort in a Microsoft 365 formula as well:

=LET(N,COUNT(A:A),sortedRange,SORT(A$2:INDEX(A:A,N+1)),IFS(OR(C2<0,C2>1),"Out of range",C2=0,INDEX(sortedRange,1),C2=1,INDEX(sortedRange,N),TRUE,(INDEX(sortedRange,CEILING(C2*N,1))+INDEX(sortedRange,FLOOR(C2*N+1,1)))/2))