Interquartile range function giving wrong results

[gko_87]

I am trying to compute interquartile range of an even set of data using Excel function but I seem to be getting the wrong results. Someone kindly have a look at my attached workbook and tell me what could be wrong. Expected values in the workbook are computed using the average function.

Interquartile Range.xlsx

[joeu2004]

I am trying to compute interquartile range of an even set of data using Excel function but I seem to be getting the wrong results.

Try QUARTILE.EXC and QUARTILE.INC in Excel 2010. Does either return the expected results?

I don't have Excel 2010 or later to try. And if you need Excel 2007 compatibility, you must use QUARTILE anyway, which I think is compatible with QUARTILE.EXC, IIRC.

The unexpected results are due to the way that Excel interpolates because 25% and 75% are not multiples of 1/(n-1).

I would agree with your expectations. But off-hand, I cannot find the actual formula that Excel uses. It used to be in the help page, IIRC. It might be in a KB now.

Oh, but googling for "excel percentile interpolation" without quotes, I discovered the following that I wrote in Aug 2010 (not sure for which version of Excel):

The following is what I inferred from one example some months ago. I
hope it helps.

Consider the following 20 values in A1:A20:
{4,4,5,5,5,5,6,6,6,7,7,7,8,8,9,9,9,10,10,10}.

Compute the 85% percentile.

Excel's PERCENTILE(A1:A20,85%) results in 9.15.

It appears that Excel calculates that as follows (p = percentile):

i = 1 + INT((20-1)*85%) = 17
f = MOD((20-1)*85%,1) = 0.15
p = INDEX(A1:A20,i) + f * (INDEX(A1:A20,i+1) - INDEX(A1:A20,i))

When I apply that to your data in C3:C10, using 25% and 75% instead of 85%, I do indeed get 3.75 and 6.25 respectively.

Off-hand, I cannot say whether that method of interpolation is accepted practice (or at least one of them).

And bear in mind that MS has "improved" some statistical functions at least since Excel 2010, but perhaps also later.

Hope that helps a little, at least insofar as it explains what Excel 2007 is doing.

[gko_87]

I tried QUARTILE.EXC and QUARTILE.INC before coming to the forum but none seems to be returning the expected results.

[joeu2004]

I tried QUARTILE.EXC and QUARTILE.INC before coming to the forum but none seems to be returning the expected results.

Okay. Errata... In Jan 2013, I wrote:

The original PERCENTILE function and PERCENTILE.INC have the same definition. With those functions, we can ask "where in the data is the 100th %ile?". The answer is: the highest value.

But the traditional statistical definition of the kth %ile is: it is the (interpolated) data point below which k% of the data lie. With that definition, we cannot ask "where is the 100th %ile?" because there is no data point below which 100% of the data lie.
[....]
PERCENTILE.EXC tries to fit that traditional statistical definition.

And HansV wrote:

The syntax is PERCENTILE.EXC(array, k) and PERCENTILE.INC(array, k)

PERCENTILE.EXC only works if k is between 1/n and 1-1/n, where n is the number of elements in array.

PERCENTILE.INC uses a slightly less accurate algorithm, but it works for any value of k between 0 and 1.

Again, I am not in a position to verify any of this now. (Sigh.)

[gko_87]

After doing some search, I found the following formulas and they seem to be working fine.

For Q1:

=IF(ISEVEN(ROUNDDOWN(COUNT(C3:C10)/2,0)),AVERAGE(SMALL(C3:C10,ROUNDDOWN(COUNT(C3:C10)/2,0)/2),SMALL(C3:C10,ROUNDDOWN(COUNT(C3:C10)/2,0)/2+1)),SMALL(C3:C10,ROUNDUP(ROUNDDOWN(COUNT(C3:C10)/2,0)/2,0)))

For Q3:

=IF(ISEVEN(ROUNDDOWN(COUNT(C3:C10)/2,0)),AVERAGE(LARGE(C3:C10,ROUNDDOWN(COUNT(C3:C10)/2,0)/2),LARGE(C3:C10,ROUNDDOWN(COUNT(C3:C10)/2,0)/2+1)),LARGE(C3:C10,ROUNDUP(ROUNDDOWN(COUNT(C3:C10)/2,0)/2,0)))