What is the difference between PERCENTILE and PERCENTILE.EXC?

[VAer]

What is the difference between PERCENTILE and PERCENTILE.EXC? Could someone explain more in math? I tried both function on same range of data, and the return values are slightly different.

Edit: Maybe I should also ask why there is error for cell D16.

https://support.microsoft.com/en-us/...e-d626debdddca

https://support.microsoft.com/en-us/...f-c31dc5dce4ba

[AliGW]

In your first link, it says this:

This function has been replaced with one or more new functions that may provide improved accuracy and whose names better reflect their usage. Although this function is still available for backward compatibility, you should consider using the new functions from now on, because this function may not be available in future versions of Excel.

PERCENTILE.INC replaces PERCENTILE.

https://bettersolutions.com/excel/fu...-functions.htm

[VAer]

In your first link, it says this:



PERCENTILE.INC replaces PERCENTILE.

https://bettersolutions.com/excel/fu...-functions.htm

Then maybe I should ask the difference between PERCENTILE.INC and PERCENTILE.EXC

Maybe I should also ask why there is error for cell D16.

[Glenn Kennedy]

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



PERCENTILE.EXC only works if k is between 1/(+1)n and 1-1/(n+1), where n is the number of elements in array. In your sample there were 5 values, so 1-1/(n+1)=.83333, so Percentile.eXc fails when you chose a value of 0.95. Similarly it fails if k<0.166667

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