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

%3CLINGO-SUB%20id%3D%22lingo-sub-916171%22%20slang%3D%22de-DE%22%3EDefinition%20of%20PERCENTILE.%20EXC%20(in%20german%20QUANTIL.%20EXKL)%20and%20related%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-916171%22%20slang%3D%22de-DE%22%3E%3CP%20align%3D%22left%22%3E%3CFONT%20color%3D%22%23000000%22%3E%3CFONT%20face%3D%22Segoe%20UI%2C%20Segoe%20UI%20Web%2C%20Segoe%20UI%20Symbol%2C%20Dev%20Center%20MDL2%20Assets%2C%20Membership%20MDL2%20Assets%2C%20Helvetica%20Neue%2C%20BBAlpha%20Sans%2C%20S60%20Sans%2C%20Arial%2C%20sans-serif%22%3EHello%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20that%20the%20description%20of%20the%20PERCENTILE.%20EXC%20(QUANTIL.%20EXKL%20in%20german)%20is%20not%20right%20(at%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fpercentile-exc-function-bbaa7204-e9e1-4010-85bf-c31dc5dce4ba%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fpercentile-exc-function-bbaa7204-e9e1-4010-85bf-c31dc5dce4ba%3C%2FA%3E)%20(in%20german%20at%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fde-de%2Farticle%2FQUANTIL-%2520EXKL-Funktion-bbaa7204-e9e1-4010-85bf-c31dc5dce4ba%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fde-de%2Farticle%2FQUANTIL-%3C%2FA%3E%3CBR%20%2F%3E%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fde-de%2Farticle%2FQUANTIL-%2520EXKL-Funktion-bbaa7204-e9e1-4010-85bf-c31dc5dce4ba%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20EXKL%20function-bbaa7204-e9e1-4010-85bf-c31dc5dce4ba%3C%2FA%3E%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20my%20opinion%2C%20PERCENTILE.%20EXC%20uses%20multiples%20of%201%2F(n%2B1)%20for%20the%20interpolation%20in%20contrast%20to%20PERCENTILE.%20INC%2C%20which%20uses%20multiples%20of%201%2F(n-1).%3C%2FP%3E%3CP%3EIn%20the%20description%20of%20PERCENTILE.%20EXC%20(QUANTIL.%20EXKL)%2C%20however%2C%20multiples%20of%201%20%2F%20(n-%201)%20would%20lead%20to%20a%20false%20result%2C%20since%20there%20is%20a%20copy-paste%20error%20from%20.%20INC%20to%20.%20Exc%3F%3C%2FP%3E%3CP%3E(see%20%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-percentile.inc-function%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-percentile.inc-function%3C%2FA%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20my%20calculated%20examples%20my%20hypothesis%20holds%20and%20the%20calculation%20for%20PERCENTILE.%20EXC%20would%20be%26lt%3B%26gt%3B%26lt%3B1%20the%3D%22%22%20same%3D%22%22%20as%3D%22%22%20the%3D%22%22%20definition%3D%22%22%20in%3D%22%22%20the%3D%22%22%20java%3D%22%22%20class%3D%22%22%20percentile%3D%22%22%26gt%3B%3C%2FP%3E%3C%2FLINGO-BODY%3Efor%200%3CA%20href%3D%22https%3A%2520%2F%2Fcommons.apache.org%2Fproper%2Fcommons-math%2Fjavadocs%2Fapi-2.2%2Forg%2Fapache%2Fcommons%2F%20math%2Fstat%2Fdescriptive%2Frank%2FPercentile.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%3C%2FA%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2520%2F%2Fcommons.apache.org%2Fproper%2Fcommons-math%2Fjavadocs%2Fapi-2.2%2Forg%2Fapache%2Fcommons%2F%20math%2Fstat%2Fdescriptive%2Frank%2FPercentile.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%2Fcommons.apache.org%2Fproper%2Fcommons-math%2Fjavadocs%2Fapi-2.2%2Forg%2Fapache%2Fcommons%2F%3C%2FA%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2520%2F%2Fcommons.apache.org%2Fproper%2Fcommons-math%2Fjavadocs%2Fapi-2.2%2Forg%2Fapache%2Fcommons%2F%20math%2Fstat%2Fdescriptive%2Frank%2FPercentile.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Emath%2Fstat%2Fdescriptive%2Frank%2FPercentile.html%3C%2FA%3E).%3CP%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMoreover%2C%20the%20definitions%20of%20QUARTILE.%20INC%20(in%20german%20QUARTILE.%20INKL)%20(interpolation%20via%20multiples%20of%201%2F(n-1))%20and%20QUARTILE.%20EXC%20(in%20german%20QUARTILE.%20EXKL)%20(interpolation%20via%201%2F(n%2B1))%20corresponds%20and%20are%20special%20cases%20for%20p%3D0.25%20and%20p%3D0.75%20of%20PERCENTILE.%20INC%20and%20PERCENTILE.%20Exc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20question%20is%3A%20Am%20I%20right%3F%3C%2FP%3E%3CP%3EIf%20not%20so%3A%20What%20is%20the%20definition%20of%20all%20these%20mentioned%20functions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20glad%20about%20an%20answer%20for%20my%20doctoral%20thesis%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3EChild%2C%3C%2FP%3E%3CP%3ETobias%20Wiernicki-Krips%3C%2FP%3E%3CLINGO-LABS%20id%3D%22lingo-labs-916171%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
New 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
Highlighted
I've forwarded your question to the people in the Excel team responsible for the technical documentation.
Highlighted
Highlighted