Browse

PERCENTILE.EXC Function (LibreOffice Calc)

Math Intermediate LibreOffice Calc LibreOffice 3.0

The PERCENTILE.EXC function in LibreOffice Calc returns the k-th percentile of a dataset using the exclusive method. This guide explains syntax, examples, edge cases, errors, and best practices.

The **PERCENTILE.EXC** function returns the *k‑th percentile* of a dataset using the **exclusive** method, meaning the 0th and 100th percentiles are **not allowed**. This method is preferred in certain statistical fields where endpoints must be excluded.

This guide provides syntax, examples, advanced techniques, common errors, and best practices, making it one of the most complete PERCENTILE.EXC references available for LibreOffice Calc.

What the PERCENTILE.EXC Function Does

  • Returns the k‑th percentile of a dataset
  • Uses the exclusive method (k must be between 0 and 1, not including endpoints)
  • Works with numbers, ranges, and arrays
  • Useful for statistical modeling and academic analysis
  • Supports interpolation between values
  • Works across sheets

PERCENTILE.EXC is more mathematically strict than PERCENTILE.INC.

Syntax

PERCENTILE.EXC(range; k)
k must be greater than 0 and less than 1.
Example: 0.25 = 25th percentile.

Basic Examples

90th percentile (exclusive)

=PERCENTILE.EXC(A1:A100; 0.9)

25th percentile (exclusive)

=PERCENTILE.EXC(A1:A100; 0.25)

Median using PERCENTILE.EXC

=PERCENTILE.EXC(A1:A100; 0.5)

Invalid endpoints

=PERCENTILE.EXC(A1:A100; 0)   → error  
=PERCENTILE.EXC(A1:A100; 1)   → error

Advanced Examples

Percentile across sheets

=PERCENTILE.EXC((Sheet1.A1:A100; Sheet2.A1:A100); 0.9)

Percentile ignoring errors (using AGGREGATE)

=AGGREGATE(18; 2; A1:A100; 0.9)

Percentile of visible cells only (filtered data)

=AGGREGATE(18; 1; A1:A100; 0.9)

Conditional percentile (indirect)

=PERCENTILE.EXC(IF(B1:B100="North"; A1:A100); 0.9)

(Confirm with Ctrl+Shift+Enter in older Calc versions.)

Percentile excluding zeros

=PERCENTILE.EXC(IF(A1:A100<>0; A1:A100); 0.9)

Percentile using sorted helper column

=INDEX(SORT(A1:A100); ROUNDUP((COUNT(A1:A100)+1)*0.9; 0))

Percentile for statistical modeling

=PERCENTILE.EXC(Data; 0.95)

How PERCENTILE.EXC Calculates Values

  1. Sorts the dataset
  2. Computes position:
    position = (n + 1) * k
  3. Interpolates if position is not an integer
  4. Fails if position < 1 or > n

Example:
Dataset size = 10
k = 0.9
Position = (10 + 1) * 0.9 = 9.9
Result = 90% between 9th and 10th values.

Differences Between PERCENTILE.INC and PERCENTILE.EXC

Feature PERCENTILE.INC PERCENTILE.EXC
Allows k = 0 Yes No
Allows k = 1 Yes No
Formula basis (n‑1)*k + 1 (n+1)*k
Statistical strictness Standard Academic/strict
Use cases Grading, finance Research, modeling

Common Errors and Fixes

Err:502 — Invalid argument

Occurs when:

  • k ≤ 0 or k ≥ 1
  • Range contains no numeric values
  • Non-numeric text is included

Err:504 — Parameter error

Occurs when:

  • Semicolons are incorrect
  • Range is malformed

Percentile returns unexpected result

Possible causes:

  • Dataset contains zeros
  • Dataset contains errors
  • Hidden rows included

Fix:
Use AGGREGATE for visibility‑aware percentiles.

Percentile differs from PERCENTILE.INC

This is expected—EXC excludes endpoints and uses a different formula.

Best Practices

  • Use PERCENTILE.EXC for strict statistical analysis
  • Use PERCENTILE.INC for general-purpose percentile calculations
  • Use AGGREGATE for error‑tolerant or visibility‑aware percentiles
  • Use array formulas for conditional percentiles
  • Clean imported data before analysis
  • Use named ranges for cleaner formulas
PERCENTILE.EXC is ideal for academic statistics, research, and modeling where endpoints must be excluded.