PERCENTILE.INC vs PERCENTILE.EXC (Explained)

Описание

PERCENTILE.INC vs PERCENTILE.EXC

PERCENTILE.INC vs PERCENTILE.EXC - this video explains the difference of PERCENTILE.INC and PERCENTILE.EXC function in excel 2010.

PERCENTILE.EXC and PERCENTILE.INC is different only in the way the non-integer rank is calculated.

For PERCENTILE.INC (and PERCENTILE) the calculated rank is K*(N-1)+1.

For PERCENTILE.EXC the calculated rank is K*(N+1).

The functions then use linear interpolation to determine the value of the percentile function.

(For more details about the interpolation, refer to the "Linear interpolation between closest ranks" section of the Wikipedia entry.)

For a very small data set you might choose to use PERCENTILE.INC (or PERCENTILE) instead of PERCENTILE.EXC because PERCENTILE.EXC returns the #NUM! error value for values of K smaller or equal 1/(N+1) and for values of K larger or equal to N/(N+1).