Home

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
Tobias_Wiernicki-Krips
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
I've forwarded your question to the people in the Excel team responsible for the technical documentation.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies