Definition of PERCENTILE.EXC (in german QUANTIL.EXKL) and related function

Copper Contributor

Hello,

 

I think that the description of the PERCENTILE.EXC (QUANTIL.EXKL in german) is not right (at https://support.office.com/en-us/article/percentile-exc-function-bbaa7204-e9e1-4010-85bf-c31dc5dce4b...) (in german at https://support.office.com/de-de/article/QUANTIL-
EXKL-Funktion-bbaa7204-e9e1-4010-85bf-c31dc5dce4ba:(

 

In my opinion, PERCENTILE.EXC uses multiples of 1/(n+1) for the interpolation in contrast to PERCENTILE.INC, which uses multiples of 1/(n-1).

In the description of PERCENTILE.EXC (QUANTIL.EXKL), however, multiples of 1 / (n- 1) would lead to a false result, since there is a copy-paste error from .INC to .EXC?

(see also https://exceljet.net/excel-functions/excel-percentile.inc-function)

 

In my calculated examples my hypothesis holds and the calculation for PERCENTILE.EXC would be for 0<p<1 the same as the definition in the Java Class Percentile (https:
//commons.apache.org/proper/commons-math/javadocs/api-2.2/org/apache/commons/
math/stat/descriptive/rank/Percentile.html).

 

Moreover, the definitions of QUARTILE.INC (in german QUARTILE.INKL) (interpolation via multiples of 1/(n-1)) and QUARTILE.EXC (in german QUARTILE.EXKL) (interpolation via 1/(n+1)) corresponds and are special cases for p=0.25 and p=0.75 of PERCENTILE.INC and PERCENTILE.EXC.

 

My question is: Am I right?

If not so: What is the definition of all these mentioned functions?

 

I would be glad about an answer for my doctoral thesis :)

Kind regards,

Tobias Wiernicki-Krips

3 Replies
I've forwarded your question to the people in the Excel team responsible for the technical documentation.