PERCENTILE.EXC

What it does

PERCENTILE.EXC(array, k)

PERCENTILE.EXC(array,k) returns the data value at fractional position k in sorted numeric observations. It uses the exclusive convention, whose positions are based on n+1; the endpoints zero and one are excluded.

Try it

=PERCENTILE.EXC({10,20,30,40},0.25) returns 12.5: its one-based position is 5*0.25=1.25, a quarter of the distance from ten to twenty.

For these four observations, k=0.2 reaches the minimum ten and k=0.8 the maximum forty. k=0.1 is below the first supported position and gives #NUM!. Being strictly between zero and one is necessary but does not guarantee an interpolable position.

Quirks

An empty numeric set gives #NUM!; nonnumeric k gives #VALUE!. Zero and one are excluded. In exact arithmetic, n observations support fractions from 1/(n+1) through n/(n+1); outside that interval the function does not extrapolate.

Referenced text, logical values and blanks are not additional numeric observations. Zeros and repeated numbers do count, so deleting them changes the rank positions. The current helper with one observation accepts the middle position k=0.5 and returns that observation; other fractions fall outside its one-position interval. These collection and indexing rules are separate from floating-point boundary rounding.

How to implement

1. Collect and sort the admitted numeric observations, keeping duplicates. Reject an empty set.
2. Form the one-based rank h=k*(n+1) in binary64. Reject it if h<1 or h>n; no neighbouring values exist for extrapolation beyond those bounds.
3. Set j=floor(h) and u=ceil(h). An integral rank returns sorted observation j directly.
4. Otherwise interpolate lower+(h-j)*(upper-lower) between observations j and u, after converting one-based positions to array indices.

With four observations and k=0.25, h=1.25, so the result is 10+0.25*(20-10)=12.5. The inclusive convention uses 1+k*(n-1)=1.75 on the same data and consequently gives 17.5.

This is the current helper’s rank construction, with ordinary binary64 interpolation. Exact floating-point endpoint edges and last-bit agreement for arbitrary Excel data sets are not established by these worked examples. A different percentile convention cannot be repaired merely by changing output formatting.

Rust code

16 percentile_exc "PERCENTILE.EXC" (2, 2) [R "array", V "k"] |s, a, sv| { percentile(s, a, sv, true) }

No implementation has been published with a Handbook test suite for this function.

Version differences

No same-input comparison between named Excel builds is recorded for this function here. The documented behaviour below is not a measurement of every Excel release.

Alternatives

PERCENTILE.INC includes the endpoint convention. QUARTILE.EXC selects exclusive quarter positions; PERCENTRANK.EXC maps a value to a fractional standing.

Named variants

No named variants are recorded yet.